// Licensed to the Apache Software Foundation (ASF) under one
// or more contributor license agreements.  See the NOTICE file
// distributed with this work for additional information
// regarding copyright ownership.  The ASF licenses this file
// to you under the Apache License, Version 2.0 (the
// "License"); you may not use this file except in compliance
// with the License.  You may obtain a copy of the License at
//
//   http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing,
// software distributed under the License is distributed on an
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
// KIND, either express or implied.  See the License for the
// specific language governing permissions and limitations
// under the License.

package org.apache.doris.nereids.mv;

import org.apache.doris.nereids.CascadesContext;
import org.apache.doris.nereids.StatementContext;
import org.apache.doris.nereids.jobs.cascades.DeriveStatsJob;
import org.apache.doris.nereids.jobs.cascades.OptimizeGroupJob;
import org.apache.doris.nereids.memo.Group;
import org.apache.doris.nereids.memo.Memo;
import org.apache.doris.nereids.rules.RuleType;
import org.apache.doris.nereids.rules.exploration.mv.InitMaterializationContextHook;
import org.apache.doris.nereids.rules.exploration.mv.MaterializedViewUtils;
import org.apache.doris.nereids.rules.exploration.mv.PreMaterializedViewRewriter;
import org.apache.doris.nereids.rules.exploration.mv.PreMaterializedViewRewriter.PreRewriteStrategy;
import org.apache.doris.nereids.rules.exploration.mv.StructInfo;
import org.apache.doris.nereids.sqltest.SqlTestBase;
import org.apache.doris.nereids.trees.expressions.NamedExpression;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
import org.apache.doris.nereids.trees.plans.logical.LogicalRelation;
import org.apache.doris.nereids.trees.plans.logical.LogicalResultSink;
import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanRewriter;
import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanVisitor;
import org.apache.doris.nereids.util.MemoTestUtils;
import org.apache.doris.nereids.util.PlanChecker;
import org.apache.doris.qe.SessionVariable;

import com.google.common.collect.ImmutableList;
import mockit.Mock;
import mockit.MockUp;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;

import java.util.BitSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * The test for pre materialized view rewrite
 */
public class PreMaterializedViewRewriterTest extends SqlTestBase {

    @Test
    public void testShouldNotRecordTmpPlanWhenNoMv() {
        connectContext.getSessionVariable().setDisableNereidsRules("PRUNE_EMPTY_PARTITION");
        BitSet disableNereidsRules = connectContext.getSessionVariable().getDisableNereidsRules();
        new MockUp<SessionVariable>() {
            @Mock
            public BitSet getDisableNereidsRules() {
                return disableNereidsRules;
            }
        };
        connectContext.getSessionVariable().enableMaterializedViewRewrite = true;
        connectContext.getSessionVariable().enableMaterializedViewNestRewrite = true;
        connectContext.getSessionVariable().setPreMaterializedViewRewriteStrategy(
                PreRewriteStrategy.FORCE_IN_RBO.name());
        CascadesContext c1 = createCascadesContext(
                "select T1.id from T1 inner join T2 "
                        + "on T1.id = T2.id "
                        + "inner join T3 on T1.id = T3.id",
                connectContext
        );
        PlanChecker tmpPlanChecker = PlanChecker.from(c1)
                .setIsQuery()
                .analyze()
                .rewrite();

        // because no mv exists, should not record tmp plan for mv
        Assertions.assertTrue(tmpPlanChecker.getCascadesContext().getStatementContext()
                .getTmpPlanForMvRewrite().isEmpty());
    }

    @Test
    public void testExtractJoinPlan() {
        String originalSql = "SELECT \n"
                + "    l.l_orderkey,\n"
                + "    l.l_suppkey,\n"
                + "    SUM(l.l_quantity) AS total_quantity,\n"
                + "    SUM(l.l_extendedprice) AS total_extended_price\n"
                + "FROM orders o\n"
                + "INNER JOIN lineitem l ON o.o_orderkey = l.l_orderkey\n"
                + "INNER JOIN partsupp ps ON l.l_partkey = ps.ps_partkey AND l.l_suppkey = ps.ps_suppkey\n"
                + "WHERE YEAR(o.o_orderdate) = 2025\n"
                + "GROUP BY l.l_orderkey, l.l_suppkey;";
        List<String> equivalentSqlList = ImmutableList.of("SELECT \n"
                + "    l.l_orderkey,\n"
                + "    l.l_suppkey,\n"
                + "    SUM(l.l_quantity) AS total_quantity,\n"
                + "    SUM(l.l_extendedprice) AS total_extended_price\n"
                + "FROM orders o\n"
                + "JOIN lineitem l ON o.o_orderkey = l.l_orderkey\n"
                + "JOIN partsupp ps ON l.l_partkey = ps.ps_partkey AND l.l_suppkey = ps.ps_suppkey\n"
                + "WHERE o.o_orderdate >= '2025-01-01' AND o.o_orderdate < '2026-01-01'\n"
                + "GROUP BY l.l_orderkey, l.l_suppkey;");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ1() {
        String originalSql = "SELECT\n"
                + "  l_returnflag,\n"
                + "  l_linestatus,\n"
                + "  sum(l_quantity)                                       AS sum_qty,\n"
                + "  sum(l_extendedprice)                                  AS sum_base_price,\n"
                + "  sum(l_extendedprice * (1 - l_discount))               AS sum_disc_price,\n"
                + "  sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,\n"
                + "  avg(l_quantity)                                       AS avg_qty,\n"
                + "  avg(l_extendedprice)                                  AS avg_price,\n"
                + "  avg(l_discount)                                       AS avg_disc,\n"
                + "  count(*)                                              AS count_order\n"
                + "FROM\n"
                + "  lineitem\n"
                + "WHERE\n"
                + "  l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY\n"
                + "GROUP BY\n"
                + "l_returnflag,\n"
                + "l_linestatus\n"
                + "ORDER BY\n"
                + "l_returnflag,\n"
                + "l_linestatus;";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  l_returnflag,\n"
                + "  l_linestatus,\n"
                + "  sum(l_quantity)                                       AS sum_qty,\n"
                + "  sum(l_extendedprice)                                  AS sum_base_price,\n"
                + "  sum(l_extendedprice * (1 - l_discount))               AS sum_disc_price,\n"
                + "  sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,\n"
                + "  avg(l_quantity)                                       AS avg_qty,\n"
                + "  avg(l_extendedprice)                                  AS avg_price,\n"
                + "  avg(l_discount)                                       AS avg_disc,\n"
                + "  count(*)                                              AS count_order\n"
                + "FROM\n"
                + "  lineitem\n"
                + "WHERE\n"
                + "  l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY\n"
                + "GROUP BY\n"
                + "l_returnflag,\n"
                + "l_linestatus\n"
                + "ORDER BY\n"
                + "l_returnflag,\n"
                + "l_linestatus");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ2() {
        String originalSql = "SELECT\n"
                + "  s_acctbal,\n"
                + "  s_name,\n"
                + "  n_name,\n"
                + "  p_partkey,\n"
                + "  p_mfgr,\n"
                + "  s_address,\n"
                + "  s_phone,\n"
                + "  s_comment\n"
                + "FROM\n"
                + "  part,\n"
                + "  supplier,\n"
                + "  partsupp,\n"
                + "  nation,\n"
                + "  region\n"
                + "WHERE\n"
                + "  p_partkey = ps_partkey\n"
                + "  AND s_suppkey = ps_suppkey\n"
                + "  AND p_size = 15\n"
                + "  AND p_type LIKE '%BRASS'\n"
                + "  AND s_nationkey = n_nationkey\n"
                + "  AND n_regionkey = r_regionkey\n"
                + "  AND r_name = 'EUROPE'\n"
                + "  AND ps_supplycost = (\n"
                + "    SELECT min(ps_supplycost)\n"
                + "    FROM\n"
                + "      partsupp, supplier,\n"
                + "      nation, region\n"
                + "    WHERE\n"
                + "      p_partkey = ps_partkey\n"
                + "      AND s_suppkey = ps_suppkey\n"
                + "      AND s_nationkey = n_nationkey\n"
                + "      AND n_regionkey = r_regionkey\n"
                + "      AND r_name = 'EUROPE'\n"
                + "  )\n"
                + "ORDER BY\n"
                + "  s_acctbal DESC,\n"
                + "  n_name,\n"
                + "  s_name,\n"
                + "  p_partkey\n"
                + "LIMIT 100";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  s_acctbal,\n"
                + "  s_name,\n"
                + "  n_name,\n"
                + "  p_partkey,\n"
                + "  p_mfgr,\n"
                + "  s_address,\n"
                + "  s_phone,\n"
                + "  s_comment\n"
                + "FROM\n"
                + "  part,\n"
                + "  supplier,\n"
                + "  partsupp,\n"
                + "  nation,\n"
                + "  region\n"
                + "WHERE\n"
                + "  p_partkey = ps_partkey\n"
                + "  AND s_suppkey = ps_suppkey\n"
                + "  AND p_size = 15\n"
                + "  AND p_type LIKE '%BRASS'\n"
                + "  AND s_nationkey = n_nationkey\n"
                + "  AND n_regionkey = r_regionkey\n"
                + "  AND r_name = 'EUROPE'\n"
                + "  AND ps_supplycost = (\n"
                + "    SELECT min(ps_supplycost)\n"
                + "    FROM\n"
                + "      partsupp, supplier,\n"
                + "      nation, region\n"
                + "    WHERE\n"
                + "      p_partkey = ps_partkey\n"
                + "      AND s_suppkey = ps_suppkey\n"
                + "      AND s_nationkey = n_nationkey\n"
                + "      AND n_regionkey = r_regionkey\n"
                + "      AND r_name = 'EUROPE'\n"
                + "  )\n"
                + "ORDER BY\n"
                + "  s_acctbal DESC,\n"
                + "  n_name,\n"
                + "  s_name,\n"
                + "  p_partkey\n"
                + "LIMIT 100");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ3() {
        String originalSql = "SELECT\n"
                + "  l_orderkey,\n"
                + "  sum(l_extendedprice * (1 - l_discount)) AS revenue,\n"
                + "  o_orderdate,\n"
                + "  o_shippriority\n"
                + "FROM\n"
                + "  customer,\n"
                + "  orders,\n"
                + "  lineitem\n"
                + "WHERE\n"
                + "  c_mktsegment = 'BUILDING'\n"
                + "  AND c_custkey = o_custkey\n"
                + "  AND l_orderkey = o_orderkey\n"
                + "  AND o_orderdate < DATE '1995-03-15'\n"
                + "  AND l_shipdate > DATE '1995-03-15'\n"
                + "GROUP BY\n"
                + "  l_orderkey,\n"
                + "  o_orderdate,\n"
                + "  o_shippriority\n"
                + "ORDER BY\n"
                + "  revenue DESC,\n"
                + "  o_orderdate\n"
                + "LIMIT 10";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  l_orderkey,\n"
                + "  sum(l_extendedprice * (1 - l_discount)) AS revenue,\n"
                + "  o_orderdate,\n"
                + "  o_shippriority\n"
                + "FROM\n"
                + "  customer,\n"
                + "  orders,\n"
                + "  lineitem\n"
                + "WHERE\n"
                + "  c_mktsegment = 'BUILDING'\n"
                + "  AND c_custkey = o_custkey\n"
                + "  AND l_orderkey = o_orderkey\n"
                + "  AND o_orderdate < DATE '1995-03-15'\n"
                + "  AND l_shipdate > DATE '1995-03-15'\n"
                + "GROUP BY\n"
                + "  l_orderkey,\n"
                + "  o_orderdate,\n"
                + "  o_shippriority\n"
                + "ORDER BY\n"
                + "  revenue DESC,\n"
                + "  o_orderdate\n"
                + "LIMIT 10");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ4() {
        String originalSql = "SELECT\n"
                + "  o_orderpriority,\n"
                + "  count(*) AS order_count\n"
                + "FROM orders\n"
                + "WHERE\n"
                + "  o_orderdate >= DATE '1993-07-01'\n"
                + "  AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH\n"
                + "AND EXISTS (\n"
                + "SELECT *\n"
                + "FROM lineitem\n"
                + "WHERE\n"
                + "l_orderkey = o_orderkey\n"
                + "AND l_commitdate < l_receiptdate\n"
                + ")\n"
                + "GROUP BY\n"
                + "o_orderpriority\n"
                + "ORDER BY\n"
                + "o_orderpriority";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  o_orderpriority,\n"
                + "  count(*) AS order_count\n"
                + "FROM orders\n"
                + "WHERE\n"
                + "  o_orderdate >= DATE '1993-07-01'\n"
                + "  AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH\n"
                + "AND EXISTS (\n"
                + "SELECT *\n"
                + "FROM lineitem\n"
                + "WHERE\n"
                + "l_orderkey = o_orderkey\n"
                + "AND l_commitdate < l_receiptdate\n"
                + ")\n"
                + "GROUP BY\n"
                + "o_orderpriority\n"
                + "ORDER BY\n"
                + "o_orderpriority");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ5() {
        String originalSql = "SELECT\n"
                + "  n_name,\n"
                + "  sum(l_extendedprice * (1 - l_discount)) AS revenue\n"
                + "FROM\n"
                + "  customer,\n"
                + "  orders,\n"
                + "  lineitem,\n"
                + "  supplier,\n"
                + "  nation,\n"
                + "  region\n"
                + "WHERE\n"
                + "  c_custkey = o_custkey\n"
                + "  AND l_orderkey = o_orderkey\n"
                + "  AND l_suppkey = s_suppkey\n"
                + "  AND c_nationkey = s_nationkey\n"
                + "  AND s_nationkey = n_nationkey\n"
                + "  AND n_regionkey = r_regionkey\n"
                + "  AND r_name = 'ASIA'\n"
                + "  AND o_orderdate >= DATE '1994-01-01'\n"
                + "  AND o_orderdate < DATE '1994-01-01' + INTERVAL '1' YEAR\n"
                + "GROUP BY\n"
                + "n_name\n"
                + "ORDER BY\n"
                + "revenue DESC";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  n_name,\n"
                + "  sum(l_extendedprice * (1 - l_discount)) AS revenue\n"
                + "FROM\n"
                + "  customer,\n"
                + "  orders,\n"
                + "  lineitem,\n"
                + "  supplier,\n"
                + "  nation,\n"
                + "  region\n"
                + "WHERE\n"
                + "  c_custkey = o_custkey\n"
                + "  AND l_orderkey = o_orderkey\n"
                + "  AND l_suppkey = s_suppkey\n"
                + "  AND c_nationkey = s_nationkey\n"
                + "  AND s_nationkey = n_nationkey\n"
                + "  AND n_regionkey = r_regionkey\n"
                + "  AND r_name = 'ASIA'\n"
                + "  AND o_orderdate >= DATE '1994-01-01'\n"
                + "  AND o_orderdate < DATE '1994-01-01' + INTERVAL '1' YEAR\n"
                + "GROUP BY\n"
                + "n_name\n"
                + "ORDER BY\n"
                + "revenue DESC");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ6() {
        String originalSql = "SELECT sum(l_extendedprice * l_discount) AS revenue\n"
                + "FROM\n"
                + "  lineitem\n"
                + "WHERE\n"
                + "  l_shipdate >= DATE '1994-01-01'\n"
                + "  AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR\n"
                + "AND l_discount BETWEEN 0.06 - 0.01 AND .06 + 0.01\n"
                + "AND l_quantity < 24";
        List<String> equivalentSqlList = ImmutableList.of("SELECT sum(l_extendedprice * l_discount) AS revenue\n"
                + "FROM\n"
                + "  lineitem\n"
                + "WHERE\n"
                + "  l_shipdate >= DATE '1994-01-01'\n"
                + "  AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR\n"
                + "AND l_discount BETWEEN 0.06 - 0.01 AND .06 + 0.01\n"
                + "AND l_quantity < 24");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ7() {
        String originalSql = "SELECT\n"
                + "  supp_nation,\n"
                + "  cust_nation,\n"
                + "  l_year,\n"
                + "  sum(volume) AS revenue\n"
                + "FROM (\n"
                + "       SELECT\n"
                + "         n1.n_name                          AS supp_nation,\n"
                + "         n2.n_name                          AS cust_nation,\n"
                + "         extract(YEAR FROM l_shipdate)      AS l_year,\n"
                + "         l_extendedprice * (1 - l_discount) AS volume\n"
                + "       FROM\n"
                + "         supplier,\n"
                + "         lineitem,\n"
                + "         orders,\n"
                + "         customer,\n"
                + "         nation n1,\n"
                + "         nation n2\n"
                + "       WHERE\n"
                + "         s_suppkey = l_suppkey\n"
                + "         AND o_orderkey = l_orderkey\n"
                + "         AND c_custkey = o_custkey\n"
                + "         AND s_nationkey = n1.n_nationkey\n"
                + "         AND c_nationkey = n2.n_nationkey\n"
                + "         AND (\n"
                + "           (n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY')\n"
                + "           OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE')\n"
                + "         )\n"
                + "         AND l_shipdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'\n"
                + "     ) AS shipping\n"
                + "GROUP BY\n"
                + "  supp_nation,\n"
                + "  cust_nation,\n"
                + "  l_year\n"
                + "ORDER BY\n"
                + "  supp_nation,\n"
                + "  cust_nation,\n"
                + "  l_year";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  supp_nation,\n"
                + "  cust_nation,\n"
                + "  l_year,\n"
                + "  sum(volume) AS revenue\n"
                + "FROM (\n"
                + "       SELECT\n"
                + "         n1.n_name                          AS supp_nation,\n"
                + "         n2.n_name                          AS cust_nation,\n"
                + "         extract(YEAR FROM l_shipdate)      AS l_year,\n"
                + "         l_extendedprice * (1 - l_discount) AS volume\n"
                + "       FROM\n"
                + "         supplier,\n"
                + "         lineitem,\n"
                + "         orders,\n"
                + "         customer,\n"
                + "         nation n1,\n"
                + "         nation n2\n"
                + "       WHERE\n"
                + "         s_suppkey = l_suppkey\n"
                + "         AND o_orderkey = l_orderkey\n"
                + "         AND c_custkey = o_custkey\n"
                + "         AND s_nationkey = n1.n_nationkey\n"
                + "         AND c_nationkey = n2.n_nationkey\n"
                + "         AND (\n"
                + "           (n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY')\n"
                + "           OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE')\n"
                + "         )\n"
                + "         AND l_shipdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'\n"
                + "     ) AS shipping\n"
                + "GROUP BY\n"
                + "  supp_nation,\n"
                + "  cust_nation,\n"
                + "  l_year\n"
                + "ORDER BY\n"
                + "  supp_nation,\n"
                + "  cust_nation,\n"
                + "  l_year");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ8() {
        String originalSql = "SELECT\n"
                + "  o_year,\n"
                + "  sum(CASE\n"
                + "      WHEN nation = 'BRAZIL'\n"
                + "        THEN volume\n"
                + "      ELSE 0\n"
                + "      END) / sum(volume) AS mkt_share\n"
                + "FROM (\n"
                + "       SELECT\n"
                + "         extract(YEAR FROM o_orderdate)     AS o_year,\n"
                + "         l_extendedprice * (1 - l_discount) AS volume,\n"
                + "         n2.n_name                          AS nation\n"
                + "       FROM\n"
                + "         part,\n"
                + "         supplier,\n"
                + "         lineitem,\n"
                + "         orders,\n"
                + "         customer,\n"
                + "         nation n1,\n"
                + "         nation n2,\n"
                + "         region\n"
                + "       WHERE\n"
                + "         p_partkey = l_partkey\n"
                + "         AND s_suppkey = l_suppkey\n"
                + "         AND l_orderkey = o_orderkey\n"
                + "         AND o_custkey = c_custkey\n"
                + "         AND c_nationkey = n1.n_nationkey\n"
                + "         AND n1.n_regionkey = r_regionkey\n"
                + "         AND r_name = 'AMERICA'\n"
                + "         AND s_nationkey = n2.n_nationkey\n"
                + "         AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'\n"
                + "         AND p_type = 'ECONOMY ANODIZED STEEL'\n"
                + "     ) AS all_nations\n"
                + "GROUP BY\n"
                + "  o_year\n"
                + "ORDER BY\n"
                + "  o_year";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  o_year,\n"
                + "  sum(CASE\n"
                + "      WHEN nation = 'BRAZIL'\n"
                + "        THEN volume\n"
                + "      ELSE 0\n"
                + "      END) / sum(volume) AS mkt_share\n"
                + "FROM (\n"
                + "       SELECT\n"
                + "         extract(YEAR FROM o_orderdate)     AS o_year,\n"
                + "         l_extendedprice * (1 - l_discount) AS volume,\n"
                + "         n2.n_name                          AS nation\n"
                + "       FROM\n"
                + "         part,\n"
                + "         supplier,\n"
                + "         lineitem,\n"
                + "         orders,\n"
                + "         customer,\n"
                + "         nation n1,\n"
                + "         nation n2,\n"
                + "         region\n"
                + "       WHERE\n"
                + "         p_partkey = l_partkey\n"
                + "         AND s_suppkey = l_suppkey\n"
                + "         AND l_orderkey = o_orderkey\n"
                + "         AND o_custkey = c_custkey\n"
                + "         AND c_nationkey = n1.n_nationkey\n"
                + "         AND n1.n_regionkey = r_regionkey\n"
                + "         AND r_name = 'AMERICA'\n"
                + "         AND s_nationkey = n2.n_nationkey\n"
                + "         AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'\n"
                + "         AND p_type = 'ECONOMY ANODIZED STEEL'\n"
                + "     ) AS all_nations\n"
                + "GROUP BY\n"
                + "  o_year\n"
                + "ORDER BY\n"
                + "  o_year");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ9() {
        String originalSql = "SELECT\n"
                + "  nation,\n"
                + "  o_year,\n"
                + "  sum(amount) AS sum_profit\n"
                + "FROM (\n"
                + "       SELECT\n"
                + "         n_name                                                          AS nation,\n"
                + "         extract(YEAR FROM o_orderdate)                                  AS o_year,\n"
                + "         l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount\n"
                + "       FROM\n"
                + "         part,\n"
                + "         supplier,\n"
                + "         lineitem,\n"
                + "         partsupp,\n"
                + "         orders,\n"
                + "         nation\n"
                + "       WHERE\n"
                + "         s_suppkey = l_suppkey\n"
                + "         AND ps_suppkey = l_suppkey\n"
                + "         AND ps_partkey = l_partkey\n"
                + "         AND p_partkey = l_partkey\n"
                + "         AND o_orderkey = l_orderkey\n"
                + "         AND s_nationkey = n_nationkey\n"
                + "         AND p_name LIKE '%green%'\n"
                + "     ) AS profit\n"
                + "GROUP BY\n"
                + "  nation,\n"
                + "  o_year\n"
                + "ORDER BY\n"
                + "  nation,\n"
                + "  o_year DESC";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  nation,\n"
                + "  o_year,\n"
                + "  sum(amount) AS sum_profit\n"
                + "FROM (\n"
                + "       SELECT\n"
                + "         n_name                                                          AS nation,\n"
                + "         extract(YEAR FROM o_orderdate)                                  AS o_year,\n"
                + "         l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount\n"
                + "       FROM\n"
                + "         part,\n"
                + "         supplier,\n"
                + "         lineitem,\n"
                + "         partsupp,\n"
                + "         orders,\n"
                + "         nation\n"
                + "       WHERE\n"
                + "         s_suppkey = l_suppkey\n"
                + "         AND ps_suppkey = l_suppkey\n"
                + "         AND ps_partkey = l_partkey\n"
                + "         AND p_partkey = l_partkey\n"
                + "         AND o_orderkey = l_orderkey\n"
                + "         AND s_nationkey = n_nationkey\n"
                + "         AND p_name LIKE '%green%'\n"
                + "     ) AS profit\n"
                + "GROUP BY\n"
                + "  nation,\n"
                + "  o_year\n"
                + "ORDER BY\n"
                + "  nation,\n"
                + "  o_year DESC");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ10() {
        String originalSql = "SELECT\n"
                + "  c_custkey,\n"
                + "  c_name,\n"
                + "  sum(l_extendedprice * (1 - l_discount)) AS revenue,\n"
                + "  c_acctbal,\n"
                + "  n_name,\n"
                + "  c_address,\n"
                + "  c_phone,\n"
                + "  c_comment\n"
                + "FROM\n"
                + "  customer,\n"
                + "  orders,\n"
                + "  lineitem,\n"
                + "  nation\n"
                + "WHERE\n"
                + "  c_custkey = o_custkey\n"
                + "  AND l_orderkey = o_orderkey\n"
                + "  AND o_orderdate >= DATE '1993-10-01'\n"
                + "  AND o_orderdate < DATE '1993-10-01' + INTERVAL '3' MONTH\n"
                + "  AND l_returnflag = 'R'\n"
                + "  AND c_nationkey = n_nationkey\n"
                + "GROUP BY\n"
                + "  c_custkey,\n"
                + "  c_name,\n"
                + "  c_acctbal,\n"
                + "  c_phone,\n"
                + "  n_name,\n"
                + "  c_address,\n"
                + "  c_comment\n"
                + "ORDER BY\n"
                + "  revenue DESC\n"
                + "LIMIT 20";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  c_custkey,\n"
                + "  c_name,\n"
                + "  sum(l_extendedprice * (1 - l_discount)) AS revenue,\n"
                + "  c_acctbal,\n"
                + "  n_name,\n"
                + "  c_address,\n"
                + "  c_phone,\n"
                + "  c_comment\n"
                + "FROM\n"
                + "  customer,\n"
                + "  orders,\n"
                + "  lineitem,\n"
                + "  nation\n"
                + "WHERE\n"
                + "  c_custkey = o_custkey\n"
                + "  AND l_orderkey = o_orderkey\n"
                + "  AND o_orderdate >= DATE '1993-10-01'\n"
                + "  AND o_orderdate < DATE '1993-10-01' + INTERVAL '3' MONTH\n"
                + "  AND l_returnflag = 'R'\n"
                + "  AND c_nationkey = n_nationkey\n"
                + "GROUP BY\n"
                + "  c_custkey,\n"
                + "  c_name,\n"
                + "  c_acctbal,\n"
                + "  c_phone,\n"
                + "  n_name,\n"
                + "  c_address,\n"
                + "  c_comment\n"
                + "ORDER BY\n"
                + "  revenue DESC\n"
                + "LIMIT 20");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ11() {
        String originalSql = "SELECT\n"
                + "  ps_partkey,\n"
                + "  sum(ps_supplycost * ps_availqty) AS value\n"
                + "FROM\n"
                + "  partsupp,\n"
                + "  supplier,\n"
                + "  nation\n"
                + "WHERE\n"
                + "  ps_suppkey = s_suppkey\n"
                + "  AND s_nationkey = n_nationkey\n"
                + "  AND n_name = 'GERMANY'\n"
                + "GROUP BY\n"
                + "  ps_partkey\n"
                + "HAVING\n"
                + "  sum(ps_supplycost * ps_availqty) > (\n"
                + "    SELECT sum(ps_supplycost * ps_availqty) * 0.0001\n"
                + "    FROM\n"
                + "      partsupp,\n"
                + "      supplier,\n"
                + "      nation\n"
                + "    WHERE\n"
                + "      ps_suppkey = s_suppkey\n"
                + "      AND s_nationkey = n_nationkey\n"
                + "      AND n_name = 'GERMANY'\n"
                + "  )\n"
                + "ORDER BY\n"
                + "  value DESC";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  ps_partkey,\n"
                + "  sum(ps_supplycost * ps_availqty) AS value\n"
                + "FROM\n"
                + "  partsupp,\n"
                + "  supplier,\n"
                + "  nation\n"
                + "WHERE\n"
                + "  ps_suppkey = s_suppkey\n"
                + "  AND s_nationkey = n_nationkey\n"
                + "  AND n_name = 'GERMANY'\n"
                + "GROUP BY\n"
                + "  ps_partkey\n"
                + "HAVING\n"
                + "  sum(ps_supplycost * ps_availqty) > (\n"
                + "    SELECT sum(ps_supplycost * ps_availqty) * 0.0001\n"
                + "    FROM\n"
                + "      partsupp,\n"
                + "      supplier,\n"
                + "      nation\n"
                + "    WHERE\n"
                + "      ps_suppkey = s_suppkey\n"
                + "      AND s_nationkey = n_nationkey\n"
                + "      AND n_name = 'GERMANY'\n"
                + "  )\n"
                + "ORDER BY\n"
                + "  value DESC");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ12() {
        String originalSql = "SELECT\n"
                + "  l_shipmode,\n"
                + "  sum(CASE\n"
                + "      WHEN o_orderpriority = '1-URGENT'\n"
                + "           OR o_orderpriority = '2-HIGH'\n"
                + "        THEN 1\n"
                + "      ELSE 0\n"
                + "      END) AS high_line_count,\n"
                + "  sum(CASE\n"
                + "      WHEN o_orderpriority <> '1-URGENT'\n"
                + "           AND o_orderpriority <> '2-HIGH'\n"
                + "        THEN 1\n"
                + "      ELSE 0\n"
                + "      END) AS low_line_count\n"
                + "FROM\n"
                + "  orders,\n"
                + "  lineitem\n"
                + "WHERE\n"
                + "  o_orderkey = l_orderkey\n"
                + "  AND l_shipmode IN ('MAIL', 'SHIP')\n"
                + "  AND l_commitdate < l_receiptdate\n"
                + "  AND l_shipdate < l_commitdate\n"
                + "  AND l_receiptdate >= DATE '1994-01-01'\n"
                + "  AND l_receiptdate < DATE '1994-01-01' + INTERVAL '1' YEAR\n"
                + "GROUP BY\n"
                + "  l_shipmode\n"
                + "ORDER BY\n"
                + "  l_shipmode";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  l_shipmode,\n"
                + "  sum(CASE\n"
                + "      WHEN o_orderpriority = '1-URGENT'\n"
                + "           OR o_orderpriority = '2-HIGH'\n"
                + "        THEN 1\n"
                + "      ELSE 0\n"
                + "      END) AS high_line_count,\n"
                + "  sum(CASE\n"
                + "      WHEN o_orderpriority <> '1-URGENT'\n"
                + "           AND o_orderpriority <> '2-HIGH'\n"
                + "        THEN 1\n"
                + "      ELSE 0\n"
                + "      END) AS low_line_count\n"
                + "FROM\n"
                + "  orders,\n"
                + "  lineitem\n"
                + "WHERE\n"
                + "  o_orderkey = l_orderkey\n"
                + "  AND l_shipmode IN ('MAIL', 'SHIP')\n"
                + "  AND l_commitdate < l_receiptdate\n"
                + "  AND l_shipdate < l_commitdate\n"
                + "  AND l_receiptdate >= DATE '1994-01-01'\n"
                + "  AND l_receiptdate < DATE '1994-01-01' + INTERVAL '1' YEAR\n"
                + "GROUP BY\n"
                + "  l_shipmode\n"
                + "ORDER BY\n"
                + "  l_shipmode");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ13() {
        String originalSql = "SELECT\n"
                + "  c_count,\n"
                + "  count(*) AS custdist\n"
                + "FROM (\n"
                + "       SELECT\n"
                + "         c_custkey,\n"
                + "         count(o_orderkey) AS c_count\n"
                + "       FROM\n"
                + "         customer\n"
                + "         LEFT OUTER JOIN orders ON\n"
                + "                                  c_custkey = o_custkey\n"
                + "                                  AND o_comment NOT LIKE '%special%requests%'\n"
                + "       GROUP BY\n"
                + "         c_custkey\n"
                + "     ) AS c_orders\n"
                + "GROUP BY\n"
                + "  c_count\n"
                + "ORDER BY\n"
                + "  custdist DESC,\n"
                + "  c_count DESC";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  c_count,\n"
                + "  count(*) AS custdist\n"
                + "FROM (\n"
                + "       SELECT\n"
                + "         c_custkey,\n"
                + "         count(o_orderkey) AS c_count\n"
                + "       FROM\n"
                + "         customer\n"
                + "         LEFT OUTER JOIN orders ON\n"
                + "                                  c_custkey = o_custkey\n"
                + "                                  AND o_comment NOT LIKE '%special%requests%'\n"
                + "       GROUP BY\n"
                + "         c_custkey\n"
                + "     ) AS c_orders\n"
                + "GROUP BY\n"
                + "  c_count\n"
                + "ORDER BY\n"
                + "  custdist DESC,\n"
                + "  c_count DESC");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ14() {
        String originalSql = "SELECT 100.00 * sum(CASE\n"
                + "                    WHEN p_type LIKE 'PROMO%'\n"
                + "                      THEN l_extendedprice * (1 - l_discount)\n"
                + "                    ELSE 0\n"
                + "                    END) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue\n"
                + "FROM\n"
                + "  lineitem,\n"
                + "  part\n"
                + "WHERE\n"
                + "  l_partkey = p_partkey\n"
                + "  AND l_shipdate >= DATE '1995-09-01'\n"
                + "  AND l_shipdate < DATE '1995-09-01' + INTERVAL '1' MONTH";
        List<String> equivalentSqlList = ImmutableList.of("SELECT 100.00 * sum(CASE\n"
                + "                    WHEN p_type LIKE 'PROMO%'\n"
                + "                      THEN l_extendedprice * (1 - l_discount)\n"
                + "                    ELSE 0\n"
                + "                    END) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue\n"
                + "FROM\n"
                + "  lineitem,\n"
                + "  part\n"
                + "WHERE\n"
                + "  l_partkey = p_partkey\n"
                + "  AND l_shipdate >= DATE '1995-09-01'\n"
                + "  AND l_shipdate < DATE '1995-09-01' + INTERVAL '1' MONTH");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ16() {
        String originalSql = "SELECT\n"
                + "  p_brand,\n"
                + "  p_type,\n"
                + "  p_size,\n"
                + "  count(DISTINCT ps_suppkey) AS supplier_cnt\n"
                + "FROM\n"
                + "  partsupp,\n"
                + "  part\n"
                + "WHERE\n"
                + "  p_partkey = ps_partkey\n"
                + "  AND p_brand <> 'Brand#45'\n"
                + "  AND p_type NOT LIKE 'MEDIUM POLISHED%'\n"
                + "  AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)\n"
                + "  AND ps_suppkey NOT IN (\n"
                + "    SELECT s_suppkey\n"
                + "    FROM\n"
                + "      supplier\n"
                + "    WHERE\n"
                + "      s_comment LIKE '%Customer%Complaints%'\n"
                + "  )\n"
                + "GROUP BY\n"
                + "  p_brand,\n"
                + "  p_type,\n"
                + "  p_size\n"
                + "ORDER BY\n"
                + "  supplier_cnt DESC,\n"
                + "  p_brand,\n"
                + "  p_type,\n"
                + "  p_size";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  p_brand,\n"
                + "  p_type,\n"
                + "  p_size,\n"
                + "  count(DISTINCT ps_suppkey) AS supplier_cnt\n"
                + "FROM\n"
                + "  partsupp,\n"
                + "  part\n"
                + "WHERE\n"
                + "  p_partkey = ps_partkey\n"
                + "  AND p_brand <> 'Brand#45'\n"
                + "  AND p_type NOT LIKE 'MEDIUM POLISHED%'\n"
                + "  AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)\n"
                + "  AND ps_suppkey NOT IN (\n"
                + "    SELECT s_suppkey\n"
                + "    FROM\n"
                + "      supplier\n"
                + "    WHERE\n"
                + "      s_comment LIKE '%Customer%Complaints%'\n"
                + "  )\n"
                + "GROUP BY\n"
                + "  p_brand,\n"
                + "  p_type,\n"
                + "  p_size\n"
                + "ORDER BY\n"
                + "  supplier_cnt DESC,\n"
                + "  p_brand,\n"
                + "  p_type,\n"
                + "  p_size");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ17() {
        String originalSql = "SELECT sum(l_extendedprice) / 7.0 AS avg_yearly\n"
                + "FROM\n"
                + "  lineitem,\n"
                + "  part\n"
                + "WHERE\n"
                + "  p_partkey = l_partkey\n"
                + "  AND p_brand = 'Brand#23'\n"
                + "  AND p_container = 'MED BOX'\n"
                + "  AND l_quantity < (\n"
                + "    SELECT 0.2 * avg(l_quantity)\n"
                + "    FROM\n"
                + "      lineitem\n"
                + "    WHERE\n"
                + "      l_partkey = p_partkey\n"
                + "  )";
        List<String> equivalentSqlList = ImmutableList.of("SELECT sum(l_extendedprice) / 7.0 AS avg_yearly\n"
                + "FROM\n"
                + "  lineitem,\n"
                + "  part\n"
                + "WHERE\n"
                + "  p_partkey = l_partkey\n"
                + "  AND p_brand = 'Brand#23'\n"
                + "  AND p_container = 'MED BOX'\n"
                + "  AND l_quantity < (\n"
                + "    SELECT 0.2 * avg(l_quantity)\n"
                + "    FROM\n"
                + "      lineitem\n"
                + "    WHERE\n"
                + "      l_partkey = p_partkey\n"
                + "  )");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ18() {
        String originalSql = "SELECT\n"
                + "  c_name,\n"
                + "  c_custkey,\n"
                + "  o_orderkey,\n"
                + "  o_orderdate,\n"
                + "  o_totalprice,\n"
                + "  sum(l_quantity)\n"
                + "FROM\n"
                + "  customer,\n"
                + "  orders,\n"
                + "  lineitem\n"
                + "WHERE\n"
                + "  o_orderkey IN (\n"
                + "    SELECT l_orderkey\n"
                + "    FROM\n"
                + "      lineitem\n"
                + "    GROUP BY\n"
                + "      l_orderkey\n"
                + "    HAVING\n"
                + "      sum(l_quantity) > 300\n"
                + "  )\n"
                + "  AND c_custkey = o_custkey\n"
                + "  AND o_orderkey = l_orderkey\n"
                + "GROUP BY\n"
                + "  c_name,\n"
                + "  c_custkey,\n"
                + "  o_orderkey,\n"
                + "  o_orderdate,\n"
                + "  o_totalprice\n"
                + "ORDER BY\n"
                + "  o_totalprice DESC,\n"
                + "  o_orderdate\n"
                + "LIMIT 100";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  c_name,\n"
                + "  c_custkey,\n"
                + "  o_orderkey,\n"
                + "  o_orderdate,\n"
                + "  o_totalprice,\n"
                + "  sum(l_quantity)\n"
                + "FROM\n"
                + "  customer,\n"
                + "  orders,\n"
                + "  lineitem\n"
                + "WHERE\n"
                + "  o_orderkey IN (\n"
                + "    SELECT l_orderkey\n"
                + "    FROM\n"
                + "      lineitem\n"
                + "    GROUP BY\n"
                + "      l_orderkey\n"
                + "    HAVING\n"
                + "      sum(l_quantity) > 300\n"
                + "  )\n"
                + "  AND c_custkey = o_custkey\n"
                + "  AND o_orderkey = l_orderkey\n"
                + "GROUP BY\n"
                + "  c_name,\n"
                + "  c_custkey,\n"
                + "  o_orderkey,\n"
                + "  o_orderdate,\n"
                + "  o_totalprice\n"
                + "ORDER BY\n"
                + "  o_totalprice DESC,\n"
                + "  o_orderdate\n"
                + "LIMIT 100");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ19() {
        String originalSql = "SELECT sum(l_extendedprice * (1 - l_discount)) AS revenue\n"
                + "FROM\n"
                + "  lineitem,\n"
                + "  part\n"
                + "WHERE\n"
                + "  (\n"
                + "    p_partkey = l_partkey\n"
                + "    AND p_brand = 'Brand#12'\n"
                + "    AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')\n"
                + "    AND l_quantity >= 1 AND l_quantity <= 1 + 10\n"
                + "    AND p_size BETWEEN 1 AND 5\n"
                + "    AND l_shipmode IN ('AIR', 'AIR REG')\n"
                + "    AND l_shipinstruct = 'DELIVER IN PERSON'\n"
                + "  )\n"
                + "  OR\n"
                + "  (\n"
                + "    p_partkey = l_partkey\n"
                + "    AND p_brand = 'Brand#23'\n"
                + "    AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')\n"
                + "    AND l_quantity >= 10 AND l_quantity <= 10 + 10\n"
                + "    AND p_size BETWEEN 1 AND 10\n"
                + "    AND l_shipmode IN ('AIR', 'AIR REG')\n"
                + "    AND l_shipinstruct = 'DELIVER IN PERSON'\n"
                + "  )\n"
                + "  OR\n"
                + "  (\n"
                + "    p_partkey = l_partkey\n"
                + "    AND p_brand = 'Brand#34'\n"
                + "    AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')\n"
                + "    AND l_quantity >= 20 AND l_quantity <= 20 + 10\n"
                + "    AND p_size BETWEEN 1 AND 15\n"
                + "    AND l_shipmode IN ('AIR', 'AIR REG')\n"
                + "    AND l_shipinstruct = 'DELIVER IN PERSON'\n"
                + "  )";
        List<String> equivalentSqlList = ImmutableList.of("SELECT sum(l_extendedprice * (1 - l_discount)) AS revenue\n"
                + "FROM\n"
                + "  lineitem,\n"
                + "  part\n"
                + "WHERE\n"
                + "  (\n"
                + "    p_partkey = l_partkey\n"
                + "    AND p_brand = 'Brand#12'\n"
                + "    AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')\n"
                + "    AND l_quantity >= 1 AND l_quantity <= 1 + 10\n"
                + "    AND p_size BETWEEN 1 AND 5\n"
                + "    AND l_shipmode IN ('AIR', 'AIR REG')\n"
                + "    AND l_shipinstruct = 'DELIVER IN PERSON'\n"
                + "  )\n"
                + "  OR\n"
                + "  (\n"
                + "    p_partkey = l_partkey\n"
                + "    AND p_brand = 'Brand#23'\n"
                + "    AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')\n"
                + "    AND l_quantity >= 10 AND l_quantity <= 10 + 10\n"
                + "    AND p_size BETWEEN 1 AND 10\n"
                + "    AND l_shipmode IN ('AIR', 'AIR REG')\n"
                + "    AND l_shipinstruct = 'DELIVER IN PERSON'\n"
                + "  )\n"
                + "  OR\n"
                + "  (\n"
                + "    p_partkey = l_partkey\n"
                + "    AND p_brand = 'Brand#34'\n"
                + "    AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')\n"
                + "    AND l_quantity >= 20 AND l_quantity <= 20 + 10\n"
                + "    AND p_size BETWEEN 1 AND 15\n"
                + "    AND l_shipmode IN ('AIR', 'AIR REG')\n"
                + "    AND l_shipinstruct = 'DELIVER IN PERSON'\n"
                + "  )");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ20() {
        String originalSql = "SELECT\n"
                + "  s_name,\n"
                + "  s_address\n"
                + "FROM\n"
                + "  supplier, nation\n"
                + "WHERE\n"
                + "  s_suppkey IN (\n"
                + "    SELECT ps_suppkey\n"
                + "    FROM\n"
                + "      partsupp\n"
                + "    WHERE\n"
                + "      ps_partkey IN (\n"
                + "        SELECT p_partkey\n"
                + "        FROM\n"
                + "          part\n"
                + "        WHERE\n"
                + "          p_name LIKE 'forest%'\n"
                + "      )\n"
                + "      AND ps_availqty > (\n"
                + "        SELECT 0.5 * sum(l_quantity)\n"
                + "        FROM\n"
                + "          lineitem\n"
                + "        WHERE\n"
                + "          l_partkey = ps_partkey\n"
                + "          AND l_suppkey = ps_suppkey\n"
                + "          AND l_shipdate >= date('1994-01-01')\n"
                + "          AND l_shipdate < date('1994-01-01') + interval '1' YEAR\n"
                + ")\n"
                + ")\n"
                + "AND s_nationkey = n_nationkey\n"
                + "AND n_name = 'CANADA'\n"
                + "ORDER BY s_name";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  s_name,\n"
                + "  s_address\n"
                + "FROM\n"
                + "  supplier, nation\n"
                + "WHERE\n"
                + "  s_suppkey IN (\n"
                + "    SELECT ps_suppkey\n"
                + "    FROM\n"
                + "      partsupp\n"
                + "    WHERE\n"
                + "      ps_partkey IN (\n"
                + "        SELECT p_partkey\n"
                + "        FROM\n"
                + "          part\n"
                + "        WHERE\n"
                + "          p_name LIKE 'forest%'\n"
                + "      )\n"
                + "      AND ps_availqty > (\n"
                + "        SELECT 0.5 * sum(l_quantity)\n"
                + "        FROM\n"
                + "          lineitem\n"
                + "        WHERE\n"
                + "          l_partkey = ps_partkey\n"
                + "          AND l_suppkey = ps_suppkey\n"
                + "          AND l_shipdate >= date('1994-01-01')\n"
                + "          AND l_shipdate < date('1994-01-01') + interval '1' YEAR\n"
                + ")\n"
                + ")\n"
                + "AND s_nationkey = n_nationkey\n"
                + "AND n_name = 'CANADA'\n"
                + "ORDER BY s_name");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ21() {
        String originalSql = "SELECT\n"
                + "  s_name,\n"
                + "  count(*) AS numwait\n"
                + "FROM\n"
                + "  supplier,\n"
                + "  lineitem l1,\n"
                + "  orders,\n"
                + "  nation\n"
                + "WHERE\n"
                + "  s_suppkey = l1.l_suppkey\n"
                + "  AND o_orderkey = l1.l_orderkey\n"
                + "  AND o_orderstatus = 'F'\n"
                + "  AND l1.l_receiptdate > l1.l_commitdate\n"
                + "  AND exists(\n"
                + "    SELECT *\n"
                + "    FROM\n"
                + "      lineitem l2\n"
                + "    WHERE\n"
                + "      l2.l_orderkey = l1.l_orderkey\n"
                + "      AND l2.l_suppkey <> l1.l_suppkey\n"
                + "  )\n"
                + "  AND NOT exists(\n"
                + "    SELECT *\n"
                + "    FROM\n"
                + "      lineitem l3\n"
                + "    WHERE\n"
                + "      l3.l_orderkey = l1.l_orderkey\n"
                + "      AND l3.l_suppkey <> l1.l_suppkey\n"
                + "      AND l3.l_receiptdate > l3.l_commitdate\n"
                + "  )\n"
                + "  AND s_nationkey = n_nationkey\n"
                + "  AND n_name = 'SAUDI ARABIA'\n"
                + "GROUP BY\n"
                + "  s_name\n"
                + "ORDER BY\n"
                + "  numwait DESC,\n"
                + "  s_name\n"
                + "LIMIT 100";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  s_name,\n"
                + "  count(*) AS numwait\n"
                + "FROM\n"
                + "  supplier,\n"
                + "  lineitem l1,\n"
                + "  orders,\n"
                + "  nation\n"
                + "WHERE\n"
                + "  s_suppkey = l1.l_suppkey\n"
                + "  AND o_orderkey = l1.l_orderkey\n"
                + "  AND o_orderstatus = 'F'\n"
                + "  AND l1.l_receiptdate > l1.l_commitdate\n"
                + "  AND exists(\n"
                + "    SELECT *\n"
                + "    FROM\n"
                + "      lineitem l2\n"
                + "    WHERE\n"
                + "      l2.l_orderkey = l1.l_orderkey\n"
                + "      AND l2.l_suppkey <> l1.l_suppkey\n"
                + "  )\n"
                + "  AND NOT exists(\n"
                + "    SELECT *\n"
                + "    FROM\n"
                + "      lineitem l3\n"
                + "    WHERE\n"
                + "      l3.l_orderkey = l1.l_orderkey\n"
                + "      AND l3.l_suppkey <> l1.l_suppkey\n"
                + "      AND l3.l_receiptdate > l3.l_commitdate\n"
                + "  )\n"
                + "  AND s_nationkey = n_nationkey\n"
                + "  AND n_name = 'SAUDI ARABIA'\n"
                + "GROUP BY\n"
                + "  s_name\n"
                + "ORDER BY\n"
                + "  numwait DESC,\n"
                + "  s_name\n"
                + "LIMIT 100");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCHQ22() {
        String originalSql = "SELECT\n"
                + "  cntrycode,\n"
                + "  count(*)       AS numcust,\n"
                + "  sum(c_acctbal) AS totacctbal\n"
                + "FROM (\n"
                + "       SELECT\n"
                + "         substr(c_phone, 1, 2) AS cntrycode,\n"
                + "         c_acctbal\n"
                + "       FROM\n"
                + "         customer\n"
                + "       WHERE\n"
                + "         substr(c_phone, 1, 2) IN\n"
                + "         ('13', '31', '23', '29', '30', '18', '17')\n"
                + "         AND c_acctbal > (\n"
                + "           SELECT avg(c_acctbal)\n"
                + "           FROM\n"
                + "             customer\n"
                + "           WHERE\n"
                + "             c_acctbal > 0.00\n"
                + "             AND substr(c_phone, 1, 2) IN\n"
                + "                 ('13', '31', '23', '29', '30', '18', '17')\n"
                + "         )\n"
                + "         AND NOT exists(\n"
                + "           SELECT *\n"
                + "           FROM\n"
                + "             orders\n"
                + "           WHERE\n"
                + "             o_custkey = c_custkey\n"
                + "         )\n"
                + "     ) AS custsale\n"
                + "GROUP BY\n"
                + "  cntrycode\n"
                + "ORDER BY\n"
                + "  cntrycode";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  cntrycode,\n"
                + "  count(*)       AS numcust,\n"
                + "  sum(c_acctbal) AS totacctbal\n"
                + "FROM (\n"
                + "       SELECT\n"
                + "         substr(c_phone, 1, 2) AS cntrycode,\n"
                + "         c_acctbal\n"
                + "       FROM\n"
                + "         customer\n"
                + "       WHERE\n"
                + "         substr(c_phone, 1, 2) IN\n"
                + "         ('13', '31', '23', '29', '30', '18', '17')\n"
                + "         AND c_acctbal > (\n"
                + "           SELECT avg(c_acctbal)\n"
                + "           FROM\n"
                + "             customer\n"
                + "           WHERE\n"
                + "             c_acctbal > 0.00\n"
                + "             AND substr(c_phone, 1, 2) IN\n"
                + "                 ('13', '31', '23', '29', '30', '18', '17')\n"
                + "         )\n"
                + "         AND NOT exists(\n"
                + "           SELECT *\n"
                + "           FROM\n"
                + "             orders\n"
                + "           WHERE\n"
                + "             o_custkey = c_custkey\n"
                + "         )\n"
                + "     ) AS custsale\n"
                + "GROUP BY\n"
                + "  cntrycode\n"
                + "ORDER BY\n"
                + "  cntrycode");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCDsQ33() {
        String originalSql = "WITH\n"
                + "  ss AS (\n"
                + "   SELECT\n"
                + "     i_manufact_id\n"
                + "   , sum(ss_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_manufact_id IN (\n"
                + "      SELECT i_manufact_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_category IN ('Electronics'))\n"
                + "   ))\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 5)\n"
                + "      AND (ss_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_manufact_id\n"
                + ")\n"
                + ", cs AS (\n"
                + "   SELECT\n"
                + "     i_manufact_id\n"
                + "   , sum(cs_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     catalog_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_manufact_id IN (\n"
                + "      SELECT i_manufact_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_category IN ('Electronics'))\n"
                + "   ))\n"
                + "      AND (cs_item_sk = i_item_sk)\n"
                + "      AND (cs_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 5)\n"
                + "      AND (cs_bill_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_manufact_id\n"
                + ")\n"
                + ", ws AS (\n"
                + "   SELECT\n"
                + "     i_manufact_id\n"
                + "   , sum(ws_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     web_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_manufact_id IN (\n"
                + "      SELECT i_manufact_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_category IN ('Electronics'))\n"
                + "   ))\n"
                + "      AND (ws_item_sk = i_item_sk)\n"
                + "      AND (ws_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 5)\n"
                + "      AND (ws_bill_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_manufact_id\n"
                + ")\n"
                + "SELECT\n"
                + "  i_manufact_id\n"
                + ", sum(total_sales) total_sales\n"
                + "FROM\n"
                + "  (\n"
                + "   SELECT *\n"
                + "   FROM\n"
                + "     ss\n"
                + "UNION ALL    SELECT *\n"
                + "   FROM\n"
                + "     cs\n"
                + "UNION ALL    SELECT *\n"
                + "   FROM\n"
                + "     ws\n"
                + ")  tmp1\n"
                + "GROUP BY i_manufact_id\n"
                + "ORDER BY total_sales ASC\n"
                + "LIMIT 100\n";
        List<String> equivalentSqlList = ImmutableList.of("WITH\n"
                + "  ss AS (\n"
                + "   SELECT\n"
                + "     i_manufact_id\n"
                + "   , sum(ss_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_manufact_id IN (\n"
                + "      SELECT i_manufact_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_category IN ('Electronics'))\n"
                + "   ))\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 5)\n"
                + "      AND (ss_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_manufact_id\n"
                + ")\n"
                + ", cs AS (\n"
                + "   SELECT\n"
                + "     i_manufact_id\n"
                + "   , sum(cs_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     catalog_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_manufact_id IN (\n"
                + "      SELECT i_manufact_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_category IN ('Electronics'))\n"
                + "   ))\n"
                + "      AND (cs_item_sk = i_item_sk)\n"
                + "      AND (cs_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 5)\n"
                + "      AND (cs_bill_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_manufact_id\n"
                + ")\n"
                + ", ws AS (\n"
                + "   SELECT\n"
                + "     i_manufact_id\n"
                + "   , sum(ws_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     web_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_manufact_id IN (\n"
                + "      SELECT i_manufact_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_category IN ('Electronics'))\n"
                + "   ))\n"
                + "      AND (ws_item_sk = i_item_sk)\n"
                + "      AND (ws_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 5)\n"
                + "      AND (ws_bill_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_manufact_id\n"
                + ")\n"
                + "SELECT\n"
                + "  i_manufact_id\n"
                + ", sum(total_sales) total_sales\n"
                + "FROM\n"
                + "  (\n"
                + "   SELECT *\n"
                + "   FROM\n"
                + "     ss\n"
                + "UNION ALL    SELECT *\n"
                + "   FROM\n"
                + "     cs\n"
                + "UNION ALL    SELECT *\n"
                + "   FROM\n"
                + "     ws\n"
                + ")  tmp1\n"
                + "GROUP BY i_manufact_id\n"
                + "ORDER BY total_sales ASC\n"
                + "LIMIT 100\n");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCDsQ56() {
        String originalSql = "WITH\n"
                + "  ss AS (\n"
                + "   SELECT\n"
                + "     i_item_id\n"
                + "   , sum(ss_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_item_id IN (\n"
                + "      SELECT i_item_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_color IN ('slate'      , 'blanched'      , 'burnished'))\n"
                + "   ))\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 2001)\n"
                + "      AND (d_moy = 2)\n"
                + "      AND (ss_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", cs AS (\n"
                + "   SELECT\n"
                + "     i_item_id\n"
                + "   , sum(cs_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     catalog_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_item_id IN (\n"
                + "      SELECT i_item_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_color IN ('slate'      , 'blanched'      , 'burnished'))\n"
                + "   ))\n"
                + "      AND (cs_item_sk = i_item_sk)\n"
                + "      AND (cs_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 2001)\n"
                + "      AND (d_moy = 2)\n"
                + "      AND (cs_bill_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", ws AS (\n"
                + "   SELECT\n"
                + "     i_item_id\n"
                + "   , sum(ws_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     web_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_item_id IN (\n"
                + "      SELECT i_item_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_color IN ('slate'      , 'blanched'      , 'burnished'))\n"
                + "   ))\n"
                + "      AND (ws_item_sk = i_item_sk)\n"
                + "      AND (ws_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 2001)\n"
                + "      AND (d_moy = 2)\n"
                + "      AND (ws_bill_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + "SELECT\n"
                + "  i_item_id\n"
                + ", sum(total_sales) total_sales\n"
                + "FROM\n"
                + "  (\n"
                + "   SELECT *\n"
                + "   FROM\n"
                + "     ss\n"
                + "UNION ALL    SELECT *\n"
                + "   FROM\n"
                + "     cs\n"
                + "UNION ALL    SELECT *\n"
                + "   FROM\n"
                + "     ws\n"
                + ")  tmp1\n"
                + "GROUP BY i_item_id\n"
                + "ORDER BY total_sales ASC, i_item_id ASC\n"
                + "LIMIT 100\n";
        List<String> equivalentSqlList = ImmutableList.of("WITH\n"
                + "  ss AS (\n"
                + "   SELECT\n"
                + "     i_item_id\n"
                + "   , sum(ss_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_item_id IN (\n"
                + "      SELECT i_item_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_color IN ('slate'      , 'blanched'      , 'burnished'))\n"
                + "   ))\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 2001)\n"
                + "      AND (d_moy = 2)\n"
                + "      AND (ss_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", cs AS (\n"
                + "   SELECT\n"
                + "     i_item_id\n"
                + "   , sum(cs_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     catalog_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_item_id IN (\n"
                + "      SELECT i_item_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_color IN ('slate'      , 'blanched'      , 'burnished'))\n"
                + "   ))\n"
                + "      AND (cs_item_sk = i_item_sk)\n"
                + "      AND (cs_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 2001)\n"
                + "      AND (d_moy = 2)\n"
                + "      AND (cs_bill_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", ws AS (\n"
                + "   SELECT\n"
                + "     i_item_id\n"
                + "   , sum(ws_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     web_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_item_id IN (\n"
                + "      SELECT i_item_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_color IN ('slate'      , 'blanched'      , 'burnished'))\n"
                + "   ))\n"
                + "      AND (ws_item_sk = i_item_sk)\n"
                + "      AND (ws_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 2001)\n"
                + "      AND (d_moy = 2)\n"
                + "      AND (ws_bill_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + "SELECT\n"
                + "  i_item_id\n"
                + ", sum(total_sales) total_sales\n"
                + "FROM\n"
                + "  (\n"
                + "   SELECT *\n"
                + "   FROM\n"
                + "     ss\n"
                + "UNION ALL    SELECT *\n"
                + "   FROM\n"
                + "     cs\n"
                + "UNION ALL    SELECT *\n"
                + "   FROM\n"
                + "     ws\n"
                + ")  tmp1\n"
                + "GROUP BY i_item_id\n"
                + "ORDER BY total_sales ASC, i_item_id ASC\n"
                + "LIMIT 100\n");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCDsQ14() {
        String originalSql = "WITH\n"
                + "  cross_items AS (\n"
                + "   SELECT i_item_sk ss_item_sk\n"
                + "   FROM\n"
                + "     item\n"
                + "   , (\n"
                + "      SELECT\n"
                + "        iss.i_brand_id brand_id\n"
                + "      , iss.i_class_id class_id\n"
                + "      , iss.i_category_id category_id\n"
                + "      FROM\n"
                + "        store_sales\n"
                + "      , item iss\n"
                + "      , date_dim d1\n"
                + "      WHERE (ss_item_sk = iss.i_item_sk)\n"
                + "         AND (ss_sold_date_sk = d1.d_date_sk)\n"
                + "         AND (d1.d_year BETWEEN 1999 AND (1999 + 2))\n"
                + "INTERSECT       SELECT\n"
                + "        ics.i_brand_id\n"
                + "      , ics.i_class_id\n"
                + "      , ics.i_category_id\n"
                + "      FROM\n"
                + "        catalog_sales\n"
                + "      , item ics\n"
                + "      , date_dim d2\n"
                + "      WHERE (cs_item_sk = ics.i_item_sk)\n"
                + "         AND (cs_sold_date_sk = d2.d_date_sk)\n"
                + "         AND (d2.d_year BETWEEN 1999 AND (1999 + 2))\n"
                + "INTERSECT       SELECT\n"
                + "        iws.i_brand_id\n"
                + "      , iws.i_class_id\n"
                + "      , iws.i_category_id\n"
                + "      FROM\n"
                + "        web_sales\n"
                + "      , item iws\n"
                + "      , date_dim d3\n"
                + "      WHERE (ws_item_sk = iws.i_item_sk)\n"
                + "         AND (ws_sold_date_sk = d3.d_date_sk)\n"
                + "         AND (d3.d_year BETWEEN 1999 AND (1999 + 2))\n"
                + "   ) y \n"
                + "   WHERE (i_brand_id = brand_id)\n"
                + "      AND (i_class_id = class_id)\n"
                + "      AND (i_category_id = category_id)\n"
                + ")\n"
                + ", avg_sales AS (\n"
                + "   SELECT avg((quantity * list_price)) average_sales\n"
                + "   FROM\n"
                + "     (\n"
                + "      SELECT\n"
                + "        ss_quantity quantity\n"
                + "      , ss_list_price list_price\n"
                + "      FROM\n"
                + "        store_sales\n"
                + "      , date_dim\n"
                + "      WHERE (ss_sold_date_sk = d_date_sk)\n"
                + "         AND (d_year BETWEEN 1999 AND (1999 + 2))\n"
                + "UNION ALL       SELECT\n"
                + "        cs_quantity quantity\n"
                + "      , cs_list_price list_price\n"
                + "      FROM\n"
                + "        catalog_sales\n"
                + "      , date_dim\n"
                + "      WHERE (cs_sold_date_sk = d_date_sk)\n"
                + "         AND (d_year BETWEEN 1999 AND (1999 + 2))\n"
                + "UNION ALL       SELECT\n"
                + "        ws_quantity quantity\n"
                + "      , ws_list_price list_price\n"
                + "      FROM\n"
                + "        web_sales\n"
                + "      , date_dim\n"
                + "      WHERE (ws_sold_date_sk = d_date_sk)\n"
                + "         AND (d_year BETWEEN 1999 AND (1999 + 2))\n"
                + "   )  x\n"
                + ")\n"
                + "SELECT\n"
                + "  channel\n"
                + ", i_brand_id\n"
                + ", i_class_id\n"
                + ", i_category_id\n"
                + ", sum(sales)\n"
                + ", sum(number_sales)\n"
                + "FROM\n"
                + "  (\n"
                + "   SELECT\n"
                + "     'store' channel\n"
                + "   , i_brand_id\n"
                + "   , i_class_id\n"
                + "   , i_category_id\n"
                + "   , sum((ss_quantity * ss_list_price)) sales\n"
                + "   , count(*) number_sales\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (ss_item_sk IN (\n"
                + "      SELECT ss_item_sk\n"
                + "      FROM\n"
                + "        cross_items\n"
                + "   ))\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = (1999 + 2))\n"
                + "      AND (d_moy = 11)\n"
                + "   GROUP BY i_brand_id, i_class_id, i_category_id\n"
                + "   HAVING (sum((ss_quantity * ss_list_price)) > (\n"
                + "         SELECT average_sales\n"
                + "         FROM\n"
                + "           avg_sales\n"
                + "      ))\n"
                + "UNION ALL    SELECT\n"
                + "     'catalog' channel\n"
                + "   , i_brand_id\n"
                + "   , i_class_id\n"
                + "   , i_category_id\n"
                + "   , sum((cs_quantity * cs_list_price)) sales\n"
                + "   , count(*) number_sales\n"
                + "   FROM\n"
                + "     catalog_sales\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (cs_item_sk IN (\n"
                + "      SELECT ss_item_sk\n"
                + "      FROM\n"
                + "        cross_items\n"
                + "   ))\n"
                + "      AND (cs_item_sk = i_item_sk)\n"
                + "      AND (cs_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = (1999 + 2))\n"
                + "      AND (d_moy = 11)\n"
                + "   GROUP BY i_brand_id, i_class_id, i_category_id\n"
                + "   HAVING (sum((cs_quantity * cs_list_price)) > (\n"
                + "         SELECT average_sales\n"
                + "         FROM\n"
                + "           avg_sales\n"
                + "      ))\n"
                + "UNION ALL    SELECT\n"
                + "     'web' channel\n"
                + "   , i_brand_id\n"
                + "   , i_class_id\n"
                + "   , i_category_id\n"
                + "   , sum((ws_quantity * ws_list_price)) sales\n"
                + "   , count(*) number_sales\n"
                + "   FROM\n"
                + "     web_sales\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (ws_item_sk IN (\n"
                + "      SELECT ss_item_sk\n"
                + "      FROM\n"
                + "        cross_items\n"
                + "   ))\n"
                + "      AND (ws_item_sk = i_item_sk)\n"
                + "      AND (ws_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = (1999 + 2))\n"
                + "      AND (d_moy = 11)\n"
                + "   GROUP BY i_brand_id, i_class_id, i_category_id\n"
                + "   HAVING (sum((ws_quantity * ws_list_price)) > (\n"
                + "         SELECT average_sales\n"
                + "         FROM\n"
                + "           avg_sales\n"
                + "      ))\n"
                + ")  y\n"
                + "GROUP BY ROLLUP (channel, i_brand_id, i_class_id, i_category_id)\n"
                + "ORDER BY channel ASC, i_brand_id ASC, i_class_id ASC, i_category_id ASC\n"
                + "LIMIT 100\n";
        List<String> equivalentSqlList = ImmutableList.of("WITH\n"
                + "  cross_items AS (\n"
                + "   SELECT i_item_sk ss_item_sk\n"
                + "   FROM\n"
                + "     item\n"
                + "   , (\n"
                + "      SELECT\n"
                + "        iss.i_brand_id brand_id\n"
                + "      , iss.i_class_id class_id\n"
                + "      , iss.i_category_id category_id\n"
                + "      FROM\n"
                + "        store_sales\n"
                + "      , item iss\n"
                + "      , date_dim d1\n"
                + "      WHERE (ss_item_sk = iss.i_item_sk)\n"
                + "         AND (ss_sold_date_sk = d1.d_date_sk)\n"
                + "         AND (d1.d_year BETWEEN 1999 AND (1999 + 2))\n"
                + "INTERSECT       SELECT\n"
                + "        ics.i_brand_id\n"
                + "      , ics.i_class_id\n"
                + "      , ics.i_category_id\n"
                + "      FROM\n"
                + "        catalog_sales\n"
                + "      , item ics\n"
                + "      , date_dim d2\n"
                + "      WHERE (cs_item_sk = ics.i_item_sk)\n"
                + "         AND (cs_sold_date_sk = d2.d_date_sk)\n"
                + "         AND (d2.d_year BETWEEN 1999 AND (1999 + 2))\n"
                + "INTERSECT       SELECT\n"
                + "        iws.i_brand_id\n"
                + "      , iws.i_class_id\n"
                + "      , iws.i_category_id\n"
                + "      FROM\n"
                + "        web_sales\n"
                + "      , item iws\n"
                + "      , date_dim d3\n"
                + "      WHERE (ws_item_sk = iws.i_item_sk)\n"
                + "         AND (ws_sold_date_sk = d3.d_date_sk)\n"
                + "         AND (d3.d_year BETWEEN 1999 AND (1999 + 2))\n"
                + "   ) y \n"
                + "   WHERE (i_brand_id = brand_id)\n"
                + "      AND (i_class_id = class_id)\n"
                + "      AND (i_category_id = category_id)\n"
                + ")\n"
                + ", avg_sales AS (\n"
                + "   SELECT avg((quantity * list_price)) average_sales\n"
                + "   FROM\n"
                + "     (\n"
                + "      SELECT\n"
                + "        ss_quantity quantity\n"
                + "      , ss_list_price list_price\n"
                + "      FROM\n"
                + "        store_sales\n"
                + "      , date_dim\n"
                + "      WHERE (ss_sold_date_sk = d_date_sk)\n"
                + "         AND (d_year BETWEEN 1999 AND (1999 + 2))\n"
                + "UNION ALL       SELECT\n"
                + "        cs_quantity quantity\n"
                + "      , cs_list_price list_price\n"
                + "      FROM\n"
                + "        catalog_sales\n"
                + "      , date_dim\n"
                + "      WHERE (cs_sold_date_sk = d_date_sk)\n"
                + "         AND (d_year BETWEEN 1999 AND (1999 + 2))\n"
                + "UNION ALL       SELECT\n"
                + "        ws_quantity quantity\n"
                + "      , ws_list_price list_price\n"
                + "      FROM\n"
                + "        web_sales\n"
                + "      , date_dim\n"
                + "      WHERE (ws_sold_date_sk = d_date_sk)\n"
                + "         AND (d_year BETWEEN 1999 AND (1999 + 2))\n"
                + "   )  x\n"
                + ")\n"
                + "SELECT\n"
                + "  channel\n"
                + ", i_brand_id\n"
                + ", i_class_id\n"
                + ", i_category_id\n"
                + ", sum(sales)\n"
                + ", sum(number_sales)\n"
                + "FROM\n"
                + "  (\n"
                + "   SELECT\n"
                + "     'store' channel\n"
                + "   , i_brand_id\n"
                + "   , i_class_id\n"
                + "   , i_category_id\n"
                + "   , sum((ss_quantity * ss_list_price)) sales\n"
                + "   , count(*) number_sales\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (ss_item_sk IN (\n"
                + "      SELECT ss_item_sk\n"
                + "      FROM\n"
                + "        cross_items\n"
                + "   ))\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = (1999 + 2))\n"
                + "      AND (d_moy = 11)\n"
                + "   GROUP BY i_brand_id, i_class_id, i_category_id\n"
                + "   HAVING (sum((ss_quantity * ss_list_price)) > (\n"
                + "         SELECT average_sales\n"
                + "         FROM\n"
                + "           avg_sales\n"
                + "      ))\n"
                + "UNION ALL    SELECT\n"
                + "     'catalog' channel\n"
                + "   , i_brand_id\n"
                + "   , i_class_id\n"
                + "   , i_category_id\n"
                + "   , sum((cs_quantity * cs_list_price)) sales\n"
                + "   , count(*) number_sales\n"
                + "   FROM\n"
                + "     catalog_sales\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (cs_item_sk IN (\n"
                + "      SELECT ss_item_sk\n"
                + "      FROM\n"
                + "        cross_items\n"
                + "   ))\n"
                + "      AND (cs_item_sk = i_item_sk)\n"
                + "      AND (cs_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = (1999 + 2))\n"
                + "      AND (d_moy = 11)\n"
                + "   GROUP BY i_brand_id, i_class_id, i_category_id\n"
                + "   HAVING (sum((cs_quantity * cs_list_price)) > (\n"
                + "         SELECT average_sales\n"
                + "         FROM\n"
                + "           avg_sales\n"
                + "      ))\n"
                + "UNION ALL    SELECT\n"
                + "     'web' channel\n"
                + "   , i_brand_id\n"
                + "   , i_class_id\n"
                + "   , i_category_id\n"
                + "   , sum((ws_quantity * ws_list_price)) sales\n"
                + "   , count(*) number_sales\n"
                + "   FROM\n"
                + "     web_sales\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (ws_item_sk IN (\n"
                + "      SELECT ss_item_sk\n"
                + "      FROM\n"
                + "        cross_items\n"
                + "   ))\n"
                + "      AND (ws_item_sk = i_item_sk)\n"
                + "      AND (ws_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = (1999 + 2))\n"
                + "      AND (d_moy = 11)\n"
                + "   GROUP BY i_brand_id, i_class_id, i_category_id\n"
                + "   HAVING (sum((ws_quantity * ws_list_price)) > (\n"
                + "         SELECT average_sales\n"
                + "         FROM\n"
                + "           avg_sales\n"
                + "      ))\n"
                + ")  y\n"
                + "GROUP BY ROLLUP (channel, i_brand_id, i_class_id, i_category_id)\n"
                + "ORDER BY channel ASC, i_brand_id ASC, i_class_id ASC, i_category_id ASC\n"
                + "LIMIT 100\n");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCDsQ58() {
        String originalSql = "WITH\n"
                + "  ss_items AS (\n"
                + "   SELECT\n"
                + "     i_item_id item_id\n"
                + "   , sum(ss_ext_sales_price) ss_item_rev\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (ss_item_sk = i_item_sk)\n"
                + "      AND (d_date IN (\n"
                + "      SELECT d_date\n"
                + "      FROM\n"
                + "        date_dim\n"
                + "      WHERE (d_week_seq = (\n"
                + "            SELECT d_week_seq\n"
                + "            FROM\n"
                + "              date_dim\n"
                + "            WHERE (d_date = CAST('2000-01-03' AS DATE))\n"
                + "         ))\n"
                + "   ))\n"
                + "      AND (ss_sold_date_sk = d_date_sk)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", cs_items AS (\n"
                + "   SELECT\n"
                + "     i_item_id item_id\n"
                + "   , sum(cs_ext_sales_price) cs_item_rev\n"
                + "   FROM\n"
                + "     catalog_sales\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (cs_item_sk = i_item_sk)\n"
                + "      AND (d_date IN (\n"
                + "      SELECT d_date\n"
                + "      FROM\n"
                + "        date_dim\n"
                + "      WHERE (d_week_seq = (\n"
                + "            SELECT d_week_seq\n"
                + "            FROM\n"
                + "              date_dim\n"
                + "            WHERE (d_date = CAST('2000-01-03' AS DATE))\n"
                + "         ))\n"
                + "   ))\n"
                + "      AND (cs_sold_date_sk = d_date_sk)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", ws_items AS (\n"
                + "   SELECT\n"
                + "     i_item_id item_id\n"
                + "   , sum(ws_ext_sales_price) ws_item_rev\n"
                + "   FROM\n"
                + "     web_sales\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (ws_item_sk = i_item_sk)\n"
                + "      AND (d_date IN (\n"
                + "      SELECT d_date\n"
                + "      FROM\n"
                + "        date_dim\n"
                + "      WHERE (d_week_seq = (\n"
                + "            SELECT d_week_seq\n"
                + "            FROM\n"
                + "              date_dim\n"
                + "            WHERE (d_date = CAST('2000-01-03' AS DATE))\n"
                + "         ))\n"
                + "   ))\n"
                + "      AND (ws_sold_date_sk = d_date_sk)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + "SELECT\n"
                + "  ss_items.item_id\n"
                + ", ss_item_rev\n"
                + ", CAST((((ss_item_rev / ((CAST(ss_item_rev AS DECIMAL(16,7)) + cs_item_rev) + ws_item_rev)) / 3) * 100) AS DECIMAL(7,2)) ss_dev\n"
                + ", cs_item_rev\n"
                + ", CAST((((cs_item_rev / ((CAST(ss_item_rev AS DECIMAL(16,7)) + cs_item_rev) + ws_item_rev)) / 3) * 100) AS DECIMAL(7,2)) cs_dev\n"
                + ", ws_item_rev\n"
                + ", CAST((((ws_item_rev / ((CAST(ss_item_rev AS DECIMAL(16,7)) + cs_item_rev) + ws_item_rev)) / 3) * 100) AS DECIMAL(7,2)) ws_dev\n"
                + ", (((ss_item_rev + cs_item_rev) + ws_item_rev) / 3) average\n"
                + "FROM\n"
                + "  ss_items\n"
                + ", cs_items\n"
                + ", ws_items\n"
                + "WHERE (ss_items.item_id = cs_items.item_id)\n"
                + "   AND (ss_items.item_id = ws_items.item_id)\n"
                + "   AND (ss_item_rev BETWEEN (CAST('0.9' AS DECIMAL(2,1)) * cs_item_rev) AND (CAST('1.1' AS DECIMAL(2,1)) * cs_item_rev))\n"
                + "   AND (ss_item_rev BETWEEN (CAST('0.9' AS DECIMAL(2,1)) * ws_item_rev) AND (CAST('1.1' AS DECIMAL(2,1)) * ws_item_rev))\n"
                + "   AND (cs_item_rev BETWEEN (CAST('0.9' AS DECIMAL(2,1)) * ss_item_rev) AND (CAST('1.1' AS DECIMAL(2,1)) * ss_item_rev))\n"
                + "   AND (cs_item_rev BETWEEN (CAST('0.9' AS DECIMAL(2,1)) * ws_item_rev) AND (CAST('1.1' AS DECIMAL(2,1)) * ws_item_rev))\n"
                + "   AND (ws_item_rev BETWEEN (CAST('0.9' AS DECIMAL(2,1)) * ss_item_rev) AND (CAST('1.1' AS DECIMAL(2,1)) * ss_item_rev))\n"
                + "   AND (ws_item_rev BETWEEN (CAST('0.9' AS DECIMAL(2,1)) * cs_item_rev) AND (CAST('1.1' AS DECIMAL(2,1)) * cs_item_rev))\n"
                + "ORDER BY ss_items.item_id ASC, ss_item_rev ASC\n"
                + "LIMIT 100\n";
        List<String> equivalentSqlList = ImmutableList.of("WITH\n"
                + "  ss_items AS (\n"
                + "   SELECT\n"
                + "     i_item_id item_id\n"
                + "   , sum(ss_ext_sales_price) ss_item_rev\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (ss_item_sk = i_item_sk)\n"
                + "      AND (d_date IN (\n"
                + "      SELECT d_date\n"
                + "      FROM\n"
                + "        date_dim\n"
                + "      WHERE (d_week_seq = (\n"
                + "            SELECT d_week_seq\n"
                + "            FROM\n"
                + "              date_dim\n"
                + "            WHERE (d_date = CAST('2000-01-03' AS DATE))\n"
                + "         ))\n"
                + "   ))\n"
                + "      AND (ss_sold_date_sk = d_date_sk)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", cs_items AS (\n"
                + "   SELECT\n"
                + "     i_item_id item_id\n"
                + "   , sum(cs_ext_sales_price) cs_item_rev\n"
                + "   FROM\n"
                + "     catalog_sales\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (cs_item_sk = i_item_sk)\n"
                + "      AND (d_date IN (\n"
                + "      SELECT d_date\n"
                + "      FROM\n"
                + "        date_dim\n"
                + "      WHERE (d_week_seq = (\n"
                + "            SELECT d_week_seq\n"
                + "            FROM\n"
                + "              date_dim\n"
                + "            WHERE (d_date = CAST('2000-01-03' AS DATE))\n"
                + "         ))\n"
                + "   ))\n"
                + "      AND (cs_sold_date_sk = d_date_sk)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", ws_items AS (\n"
                + "   SELECT\n"
                + "     i_item_id item_id\n"
                + "   , sum(ws_ext_sales_price) ws_item_rev\n"
                + "   FROM\n"
                + "     web_sales\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (ws_item_sk = i_item_sk)\n"
                + "      AND (d_date IN (\n"
                + "      SELECT d_date\n"
                + "      FROM\n"
                + "        date_dim\n"
                + "      WHERE (d_week_seq = (\n"
                + "            SELECT d_week_seq\n"
                + "            FROM\n"
                + "              date_dim\n"
                + "            WHERE (d_date = CAST('2000-01-03' AS DATE))\n"
                + "         ))\n"
                + "   ))\n"
                + "      AND (ws_sold_date_sk = d_date_sk)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + "SELECT\n"
                + "  ss_items.item_id\n"
                + ", ss_item_rev\n"
                + ", CAST((((ss_item_rev / ((CAST(ss_item_rev AS DECIMAL(16,7)) + cs_item_rev) + ws_item_rev)) / 3) * 100) AS DECIMAL(7,2)) ss_dev\n"
                + ", cs_item_rev\n"
                + ", CAST((((cs_item_rev / ((CAST(ss_item_rev AS DECIMAL(16,7)) + cs_item_rev) + ws_item_rev)) / 3) * 100) AS DECIMAL(7,2)) cs_dev\n"
                + ", ws_item_rev\n"
                + ", CAST((((ws_item_rev / ((CAST(ss_item_rev AS DECIMAL(16,7)) + cs_item_rev) + ws_item_rev)) / 3) * 100) AS DECIMAL(7,2)) ws_dev\n"
                + ", (((ss_item_rev + cs_item_rev) + ws_item_rev) / 3) average\n"
                + "FROM\n"
                + "  ss_items\n"
                + ", cs_items\n"
                + ", ws_items\n"
                + "WHERE (ss_items.item_id = cs_items.item_id)\n"
                + "   AND (ss_items.item_id = ws_items.item_id)\n"
                + "   AND (ss_item_rev BETWEEN (CAST('0.9' AS DECIMAL(2,1)) * cs_item_rev) AND (CAST('1.1' AS DECIMAL(2,1)) * cs_item_rev))\n"
                + "   AND (ss_item_rev BETWEEN (CAST('0.9' AS DECIMAL(2,1)) * ws_item_rev) AND (CAST('1.1' AS DECIMAL(2,1)) * ws_item_rev))\n"
                + "   AND (cs_item_rev BETWEEN (CAST('0.9' AS DECIMAL(2,1)) * ss_item_rev) AND (CAST('1.1' AS DECIMAL(2,1)) * ss_item_rev))\n"
                + "   AND (cs_item_rev BETWEEN (CAST('0.9' AS DECIMAL(2,1)) * ws_item_rev) AND (CAST('1.1' AS DECIMAL(2,1)) * ws_item_rev))\n"
                + "   AND (ws_item_rev BETWEEN (CAST('0.9' AS DECIMAL(2,1)) * ss_item_rev) AND (CAST('1.1' AS DECIMAL(2,1)) * ss_item_rev))\n"
                + "   AND (ws_item_rev BETWEEN (CAST('0.9' AS DECIMAL(2,1)) * cs_item_rev) AND (CAST('1.1' AS DECIMAL(2,1)) * cs_item_rev))\n"
                + "ORDER BY ss_items.item_id ASC, ss_item_rev ASC\n"
                + "LIMIT 100\n");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCDsQ80() {
        String originalSql = "WITH\n"
                + "  ssr AS (\n"
                + "   SELECT\n"
                + "     s_store_id store_id\n"
                + "   , sum(ss_ext_sales_price) sales\n"
                + "   , sum(COALESCE(sr_return_amt, 0)) returns\n"
                + "   , sum((ss_net_profit - COALESCE(sr_net_loss, 0))) profit\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   LEFT JOIN store_returns ON (ss_item_sk = sr_item_sk)\n"
                + "      AND (ss_ticket_number = sr_ticket_number)\n"
                + "   , date_dim\n"
                + "   , store\n"
                + "   , item\n"
                + "   , promotion\n"
                + "   WHERE (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (CAST(d_date AS DATE) BETWEEN CAST('2000-08-23' AS DATE) AND (CAST('2000-08-23' AS DATE) + INTERVAL  '30' DAY))\n"
                + "      AND (ss_store_sk = s_store_sk)\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (i_current_price > 50)\n"
                + "      AND (ss_promo_sk = p_promo_sk)\n"
                + "      AND (p_channel_tv = 'N')\n"
                + "   GROUP BY s_store_id\n"
                + ")\n"
                + ", csr AS (\n"
                + "   SELECT\n"
                + "     cp_catalog_page_id catalog_page_id\n"
                + "   , sum(cs_ext_sales_price) sales\n"
                + "   , sum(COALESCE(cr_return_amount, 0)) returns\n"
                + "   , sum((cs_net_profit - COALESCE(cr_net_loss, 0))) profit\n"
                + "   FROM\n"
                + "     catalog_sales\n"
                + "   LEFT JOIN catalog_returns ON (cs_item_sk = cr_item_sk)\n"
                + "      AND (cs_order_number = cr_order_number)\n"
                + "   , date_dim\n"
                + "   , catalog_page\n"
                + "   , item\n"
                + "   , promotion\n"
                + "   WHERE (cs_sold_date_sk = d_date_sk)\n"
                + "      AND (CAST(d_date AS DATE) BETWEEN CAST('2000-08-23' AS DATE) AND (CAST('2000-08-23' AS DATE) + INTERVAL  '30' DAY))\n"
                + "      AND (cs_catalog_page_sk = cp_catalog_page_sk)\n"
                + "      AND (cs_item_sk = i_item_sk)\n"
                + "      AND (i_current_price > 50)\n"
                + "      AND (cs_promo_sk = p_promo_sk)\n"
                + "      AND (p_channel_tv = 'N')\n"
                + "   GROUP BY cp_catalog_page_id\n"
                + ")\n"
                + ", wsr AS (\n"
                + "   SELECT\n"
                + "     web_site_id\n"
                + "   , sum(ws_ext_sales_price) sales\n"
                + "   , sum(COALESCE(wr_return_amt, 0)) returns\n"
                + "   , sum((ws_net_profit - COALESCE(wr_net_loss, 0))) profit\n"
                + "   FROM\n"
                + "     web_sales\n"
                + "   LEFT JOIN web_returns ON (ws_item_sk = wr_item_sk)\n"
                + "      AND (ws_order_number = wr_order_number)\n"
                + "   , date_dim\n"
                + "   , web_site\n"
                + "   , item\n"
                + "   , promotion\n"
                + "   WHERE (ws_sold_date_sk = d_date_sk)\n"
                + "      AND (CAST(d_date AS DATE) BETWEEN CAST('2000-08-23' AS DATE) AND (CAST('2000-08-23' AS DATE) + INTERVAL  '30' DAY))\n"
                + "      AND (ws_web_site_sk = web_site_sk)\n"
                + "      AND (ws_item_sk = i_item_sk)\n"
                + "      AND (i_current_price > 50)\n"
                + "      AND (ws_promo_sk = p_promo_sk)\n"
                + "      AND (p_channel_tv = 'N')\n"
                + "   GROUP BY web_site_id\n"
                + ")\n"
                + "SELECT\n"
                + "  channel\n"
                + ", id\n"
                + ", sum(sales) sales\n"
                + ", sum(returns) returns\n"
                + ", sum(profit) profit\n"
                + "FROM\n"
                + "  (\n"
                + "   SELECT\n"
                + "     'store channel' channel\n"
                + "   , concat('store', store_id) id\n"
                + "   , sales\n"
                + "   , returns\n"
                + "   , profit\n"
                + "   FROM\n"
                + "     ssr\n"
                + "UNION ALL    SELECT\n"
                + "     'catalog channel' channel\n"
                + "   , concat('catalog_page', catalog_page_id) id\n"
                + "   , sales\n"
                + "   , returns\n"
                + "   , profit\n"
                + "   FROM\n"
                + "     csr\n"
                + "UNION ALL    SELECT\n"
                + "     'web channel' channel\n"
                + "   , concat('web_site', web_site_id) id\n"
                + "   , sales\n"
                + "   , returns\n"
                + "   , profit\n"
                + "   FROM\n"
                + "     wsr\n"
                + ")  x\n"
                + "GROUP BY ROLLUP (channel, id)\n"
                + "ORDER BY channel ASC, id ASC\n"
                + "LIMIT 100\n";
        List<String> equivalentSqlList = ImmutableList.of("WITH\n"
                + "  ssr AS (\n"
                + "   SELECT\n"
                + "     s_store_id store_id\n"
                + "   , sum(ss_ext_sales_price) sales\n"
                + "   , sum(COALESCE(sr_return_amt, 0)) returns\n"
                + "   , sum((ss_net_profit - COALESCE(sr_net_loss, 0))) profit\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   LEFT JOIN store_returns ON (ss_item_sk = sr_item_sk)\n"
                + "      AND (ss_ticket_number = sr_ticket_number)\n"
                + "   , date_dim\n"
                + "   , store\n"
                + "   , item\n"
                + "   , promotion\n"
                + "   WHERE (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (CAST(d_date AS DATE) BETWEEN CAST('2000-08-23' AS DATE) AND (CAST('2000-08-23' AS DATE) + INTERVAL  '30' DAY))\n"
                + "      AND (ss_store_sk = s_store_sk)\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (i_current_price > 50)\n"
                + "      AND (ss_promo_sk = p_promo_sk)\n"
                + "      AND (p_channel_tv = 'N')\n"
                + "   GROUP BY s_store_id\n"
                + ")\n"
                + ", csr AS (\n"
                + "   SELECT\n"
                + "     cp_catalog_page_id catalog_page_id\n"
                + "   , sum(cs_ext_sales_price) sales\n"
                + "   , sum(COALESCE(cr_return_amount, 0)) returns\n"
                + "   , sum((cs_net_profit - COALESCE(cr_net_loss, 0))) profit\n"
                + "   FROM\n"
                + "     catalog_sales\n"
                + "   LEFT JOIN catalog_returns ON (cs_item_sk = cr_item_sk)\n"
                + "      AND (cs_order_number = cr_order_number)\n"
                + "   , date_dim\n"
                + "   , catalog_page\n"
                + "   , item\n"
                + "   , promotion\n"
                + "   WHERE (cs_sold_date_sk = d_date_sk)\n"
                + "      AND (CAST(d_date AS DATE) BETWEEN CAST('2000-08-23' AS DATE) AND (CAST('2000-08-23' AS DATE) + INTERVAL  '30' DAY))\n"
                + "      AND (cs_catalog_page_sk = cp_catalog_page_sk)\n"
                + "      AND (cs_item_sk = i_item_sk)\n"
                + "      AND (i_current_price > 50)\n"
                + "      AND (cs_promo_sk = p_promo_sk)\n"
                + "      AND (p_channel_tv = 'N')\n"
                + "   GROUP BY cp_catalog_page_id\n"
                + ")\n"
                + ", wsr AS (\n"
                + "   SELECT\n"
                + "     web_site_id\n"
                + "   , sum(ws_ext_sales_price) sales\n"
                + "   , sum(COALESCE(wr_return_amt, 0)) returns\n"
                + "   , sum((ws_net_profit - COALESCE(wr_net_loss, 0))) profit\n"
                + "   FROM\n"
                + "     web_sales\n"
                + "   LEFT JOIN web_returns ON (ws_item_sk = wr_item_sk)\n"
                + "      AND (ws_order_number = wr_order_number)\n"
                + "   , date_dim\n"
                + "   , web_site\n"
                + "   , item\n"
                + "   , promotion\n"
                + "   WHERE (ws_sold_date_sk = d_date_sk)\n"
                + "      AND (CAST(d_date AS DATE) BETWEEN CAST('2000-08-23' AS DATE) AND (CAST('2000-08-23' AS DATE) + INTERVAL  '30' DAY))\n"
                + "      AND (ws_web_site_sk = web_site_sk)\n"
                + "      AND (ws_item_sk = i_item_sk)\n"
                + "      AND (i_current_price > 50)\n"
                + "      AND (ws_promo_sk = p_promo_sk)\n"
                + "      AND (p_channel_tv = 'N')\n"
                + "   GROUP BY web_site_id\n"
                + ")\n"
                + "SELECT\n"
                + "  channel\n"
                + ", id\n"
                + ", sum(sales) sales\n"
                + ", sum(returns) returns\n"
                + ", sum(profit) profit\n"
                + "FROM\n"
                + "  (\n"
                + "   SELECT\n"
                + "     'store channel' channel\n"
                + "   , concat('store', store_id) id\n"
                + "   , sales\n"
                + "   , returns\n"
                + "   , profit\n"
                + "   FROM\n"
                + "     ssr\n"
                + "UNION ALL    SELECT\n"
                + "     'catalog channel' channel\n"
                + "   , concat('catalog_page', catalog_page_id) id\n"
                + "   , sales\n"
                + "   , returns\n"
                + "   , profit\n"
                + "   FROM\n"
                + "     csr\n"
                + "UNION ALL    SELECT\n"
                + "     'web channel' channel\n"
                + "   , concat('web_site', web_site_id) id\n"
                + "   , sales\n"
                + "   , returns\n"
                + "   , profit\n"
                + "   FROM\n"
                + "     wsr\n"
                + ")  x\n"
                + "GROUP BY ROLLUP (channel, id)\n"
                + "ORDER BY channel ASC, id ASC\n"
                + "LIMIT 100\n");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCDsQ60() {
        String originalSql = "WITH\n"
                + "  ss AS (\n"
                + "   SELECT\n"
                + "     i_item_id\n"
                + "   , sum(ss_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_item_id IN (\n"
                + "      SELECT i_item_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_category IN ('Music'))\n"
                + "   ))\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 9)\n"
                + "      AND (ss_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", cs AS (\n"
                + "   SELECT\n"
                + "     i_item_id\n"
                + "   , sum(cs_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     catalog_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_item_id IN (\n"
                + "      SELECT i_item_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_category IN ('Music'))\n"
                + "   ))\n"
                + "      AND (cs_item_sk = i_item_sk)\n"
                + "      AND (cs_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 9)\n"
                + "      AND (cs_bill_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", ws AS (\n"
                + "   SELECT\n"
                + "     i_item_id\n"
                + "   , sum(ws_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     web_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_item_id IN (\n"
                + "      SELECT i_item_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_category IN ('Music'))\n"
                + "   ))\n"
                + "      AND (ws_item_sk = i_item_sk)\n"
                + "      AND (ws_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 9)\n"
                + "      AND (ws_bill_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + "SELECT\n"
                + "  i_item_id\n"
                + ", sum(total_sales) total_sales\n"
                + "FROM\n"
                + "  (\n"
                + "   SELECT *\n"
                + "   FROM\n"
                + "     ss\n"
                + "UNION ALL    SELECT *\n"
                + "   FROM\n"
                + "     cs\n"
                + "UNION ALL    SELECT *\n"
                + "   FROM\n"
                + "     ws\n"
                + ")  tmp1\n"
                + "GROUP BY i_item_id\n"
                + "ORDER BY i_item_id ASC, total_sales ASC\n"
                + "LIMIT 100\n";
        List<String> equivalentSqlList = ImmutableList.of("WITH\n"
                + "  ss AS (\n"
                + "   SELECT\n"
                + "     i_item_id\n"
                + "   , sum(ss_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_item_id IN (\n"
                + "      SELECT i_item_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_category IN ('Music'))\n"
                + "   ))\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 9)\n"
                + "      AND (ss_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", cs AS (\n"
                + "   SELECT\n"
                + "     i_item_id\n"
                + "   , sum(cs_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     catalog_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_item_id IN (\n"
                + "      SELECT i_item_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_category IN ('Music'))\n"
                + "   ))\n"
                + "      AND (cs_item_sk = i_item_sk)\n"
                + "      AND (cs_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 9)\n"
                + "      AND (cs_bill_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", ws AS (\n"
                + "   SELECT\n"
                + "     i_item_id\n"
                + "   , sum(ws_ext_sales_price) total_sales\n"
                + "   FROM\n"
                + "     web_sales\n"
                + "   , date_dim\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (i_item_id IN (\n"
                + "      SELECT i_item_id\n"
                + "      FROM\n"
                + "        item\n"
                + "      WHERE (i_category IN ('Music'))\n"
                + "   ))\n"
                + "      AND (ws_item_sk = i_item_sk)\n"
                + "      AND (ws_sold_date_sk = d_date_sk)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 9)\n"
                + "      AND (ws_bill_addr_sk = ca_address_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + "SELECT\n"
                + "  i_item_id\n"
                + ", sum(total_sales) total_sales\n"
                + "FROM\n"
                + "  (\n"
                + "   SELECT *\n"
                + "   FROM\n"
                + "     ss\n"
                + "UNION ALL    SELECT *\n"
                + "   FROM\n"
                + "     cs\n"
                + "UNION ALL    SELECT *\n"
                + "   FROM\n"
                + "     ws\n"
                + ")  tmp1\n"
                + "GROUP BY i_item_id\n"
                + "ORDER BY i_item_id ASC, total_sales ASC\n"
                + "LIMIT 100\n");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCDsQ61() {
        String originalSql = "SELECT\n"
                + "  promotions\n"
                + ", total\n"
                + ", ((CAST(promotions AS DECIMAL(15,4)) / CAST(total AS DECIMAL(15,4))) * 100)\n"
                + "FROM\n"
                + "  (\n"
                + "   SELECT sum(ss_ext_sales_price) promotions\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , store\n"
                + "   , promotion\n"
                + "   , date_dim\n"
                + "   , customer_ds\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (ss_store_sk = s_store_sk)\n"
                + "      AND (ss_promo_sk = p_promo_sk)\n"
                + "      AND (ss_customer_sk = c_customer_sk)\n"
                + "      AND (ca_address_sk = c_current_addr_sk)\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "      AND (i_category = 'Jewelry')\n"
                + "      AND ((p_channel_dmail = 'Y')\n"
                + "         OR (p_channel_email = 'Y')\n"
                + "         OR (p_channel_tv = 'Y'))\n"
                + "      AND (s_gmt_offset = -5)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 11)\n"
                + ")  promotional_sales\n"
                + ", (\n"
                + "   SELECT sum(ss_ext_sales_price) total\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , store\n"
                + "   , date_dim\n"
                + "   , customer_ds\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (ss_store_sk = s_store_sk)\n"
                + "      AND (ss_customer_sk = c_customer_sk)\n"
                + "      AND (ca_address_sk = c_current_addr_sk)\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "      AND (i_category = 'Jewelry')\n"
                + "      AND (s_gmt_offset = -5)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 11)\n"
                + ")  all_sales\n"
                + "ORDER BY promotions ASC, total ASC\n"
                + "LIMIT 100\n";
        List<String> equivalentSqlList = ImmutableList.of("SELECT\n"
                + "  promotions\n"
                + ", total\n"
                + ", ((CAST(promotions AS DECIMAL(15,4)) / CAST(total AS DECIMAL(15,4))) * 100)\n"
                + "FROM\n"
                + "  (\n"
                + "   SELECT sum(ss_ext_sales_price) promotions\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , store\n"
                + "   , promotion\n"
                + "   , date_dim\n"
                + "   , customer_ds\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (ss_store_sk = s_store_sk)\n"
                + "      AND (ss_promo_sk = p_promo_sk)\n"
                + "      AND (ss_customer_sk = c_customer_sk)\n"
                + "      AND (ca_address_sk = c_current_addr_sk)\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "      AND (i_category = 'Jewelry')\n"
                + "      AND ((p_channel_dmail = 'Y')\n"
                + "         OR (p_channel_email = 'Y')\n"
                + "         OR (p_channel_tv = 'Y'))\n"
                + "      AND (s_gmt_offset = -5)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 11)\n"
                + ")  promotional_sales\n"
                + ", (\n"
                + "   SELECT sum(ss_ext_sales_price) total\n"
                + "   FROM\n"
                + "     store_sales\n"
                + "   , store\n"
                + "   , date_dim\n"
                + "   , customer_ds\n"
                + "   , customer_address\n"
                + "   , item\n"
                + "   WHERE (ss_sold_date_sk = d_date_sk)\n"
                + "      AND (ss_store_sk = s_store_sk)\n"
                + "      AND (ss_customer_sk = c_customer_sk)\n"
                + "      AND (ca_address_sk = c_current_addr_sk)\n"
                + "      AND (ss_item_sk = i_item_sk)\n"
                + "      AND (ca_gmt_offset = -5)\n"
                + "      AND (i_category = 'Jewelry')\n"
                + "      AND (s_gmt_offset = -5)\n"
                + "      AND (d_year = 1998)\n"
                + "      AND (d_moy = 11)\n"
                + ")  all_sales\n"
                + "ORDER BY promotions ASC, total ASC\n"
                + "LIMIT 100\n");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    @Test
    public void testTPCDsQ83() {
        String originalSql = "WITH\n"
                + "  sr_items AS (\n"
                + "   SELECT\n"
                + "     i_item_id item_id\n"
                + "   , sum(sr_return_quantity) sr_item_qty\n"
                + "   FROM\n"
                + "     store_returns\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (sr_item_sk = i_item_sk)\n"
                + "      AND (d_date IN (\n"
                + "      SELECT d_date\n"
                + "      FROM\n"
                + "        date_dim\n"
                + "      WHERE (d_week_seq IN (\n"
                + "         SELECT d_week_seq\n"
                + "         FROM\n"
                + "           date_dim\n"
                + "         WHERE (d_date IN (CAST('2000-06-30' AS DATE)         , CAST('2000-09-27' AS DATE)         , CAST('2000-11-17' AS DATE)))\n"
                + "      ))\n"
                + "   ))\n"
                + "      AND (sr_returned_date_sk = d_date_sk)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", cr_items AS (\n"
                + "   SELECT\n"
                + "     i_item_id item_id\n"
                + "   , sum(cr_return_quantity) cr_item_qty\n"
                + "   FROM\n"
                + "     catalog_returns\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (cr_item_sk = i_item_sk)\n"
                + "      AND (d_date IN (\n"
                + "      SELECT d_date\n"
                + "      FROM\n"
                + "        date_dim\n"
                + "      WHERE (d_week_seq IN (\n"
                + "         SELECT d_week_seq\n"
                + "         FROM\n"
                + "           date_dim\n"
                + "         WHERE (d_date IN (CAST('2000-06-30' AS DATE)         , CAST('2000-09-27' AS DATE)         , CAST('2000-11-17' AS DATE)))\n"
                + "      ))\n"
                + "   ))\n"
                + "      AND (cr_returned_date_sk = d_date_sk)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", wr_items AS (\n"
                + "   SELECT\n"
                + "     i_item_id item_id\n"
                + "   , sum(wr_return_quantity) wr_item_qty\n"
                + "   FROM\n"
                + "     web_returns\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (wr_item_sk = i_item_sk)\n"
                + "      AND (d_date IN (\n"
                + "      SELECT d_date\n"
                + "      FROM\n"
                + "        date_dim\n"
                + "      WHERE (d_week_seq IN (\n"
                + "         SELECT d_week_seq\n"
                + "         FROM\n"
                + "           date_dim\n"
                + "         WHERE (d_date IN (CAST('2000-06-30' AS DATE)         , CAST('2000-09-27' AS DATE)         , CAST('2000-11-17' AS DATE)))\n"
                + "      ))\n"
                + "   ))\n"
                + "      AND (wr_returned_date_sk = d_date_sk)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + "SELECT\n"
                + "  sr_items.item_id\n"
                + ", sr_item_qty\n"
                + ", CAST((((sr_item_qty / ((CAST(sr_item_qty AS DECIMAL(9,4)) + cr_item_qty) + wr_item_qty)) / CAST('3.0' AS DECIMAL(2,1))) * 100) AS DECIMAL(7,2)) sr_dev\n"
                + ", cr_item_qty\n"
                + ", CAST((((cr_item_qty / ((CAST(sr_item_qty AS DECIMAL(9,4)) + cr_item_qty) + wr_item_qty)) / CAST('3.0' AS DECIMAL(2,1))) * 100) AS DECIMAL(7,2)) cr_dev\n"
                + ", wr_item_qty\n"
                + ", CAST((((wr_item_qty / ((CAST(sr_item_qty AS DECIMAL(9,4)) + cr_item_qty) + wr_item_qty)) / CAST('3.0' AS DECIMAL(2,1))) * 100) AS DECIMAL(7,2)) wr_dev\n"
                + ", (((sr_item_qty + cr_item_qty) + wr_item_qty) / CAST('3.00' AS DECIMAL(5,2))) average\n"
                + "FROM\n"
                + "  sr_items\n"
                + ", cr_items\n"
                + ", wr_items\n"
                + "WHERE (sr_items.item_id = cr_items.item_id)\n"
                + "   AND (sr_items.item_id = wr_items.item_id)\n"
                + "ORDER BY sr_items.item_id ASC, sr_item_qty ASC\n"
                + "LIMIT 100\n";
        List<String> equivalentSqlList = ImmutableList.of("WITH\n"
                + "  sr_items AS (\n"
                + "   SELECT\n"
                + "     i_item_id item_id\n"
                + "   , sum(sr_return_quantity) sr_item_qty\n"
                + "   FROM\n"
                + "     store_returns\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (sr_item_sk = i_item_sk)\n"
                + "      AND (d_date IN (\n"
                + "      SELECT d_date\n"
                + "      FROM\n"
                + "        date_dim\n"
                + "      WHERE (d_week_seq IN (\n"
                + "         SELECT d_week_seq\n"
                + "         FROM\n"
                + "           date_dim\n"
                + "         WHERE (d_date IN (CAST('2000-06-30' AS DATE)         , CAST('2000-09-27' AS DATE)         , CAST('2000-11-17' AS DATE)))\n"
                + "      ))\n"
                + "   ))\n"
                + "      AND (sr_returned_date_sk = d_date_sk)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", cr_items AS (\n"
                + "   SELECT\n"
                + "     i_item_id item_id\n"
                + "   , sum(cr_return_quantity) cr_item_qty\n"
                + "   FROM\n"
                + "     catalog_returns\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (cr_item_sk = i_item_sk)\n"
                + "      AND (d_date IN (\n"
                + "      SELECT d_date\n"
                + "      FROM\n"
                + "        date_dim\n"
                + "      WHERE (d_week_seq IN (\n"
                + "         SELECT d_week_seq\n"
                + "         FROM\n"
                + "           date_dim\n"
                + "         WHERE (d_date IN (CAST('2000-06-30' AS DATE)         , CAST('2000-09-27' AS DATE)         , CAST('2000-11-17' AS DATE)))\n"
                + "      ))\n"
                + "   ))\n"
                + "      AND (cr_returned_date_sk = d_date_sk)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + ", wr_items AS (\n"
                + "   SELECT\n"
                + "     i_item_id item_id\n"
                + "   , sum(wr_return_quantity) wr_item_qty\n"
                + "   FROM\n"
                + "     web_returns\n"
                + "   , item\n"
                + "   , date_dim\n"
                + "   WHERE (wr_item_sk = i_item_sk)\n"
                + "      AND (d_date IN (\n"
                + "      SELECT d_date\n"
                + "      FROM\n"
                + "        date_dim\n"
                + "      WHERE (d_week_seq IN (\n"
                + "         SELECT d_week_seq\n"
                + "         FROM\n"
                + "           date_dim\n"
                + "         WHERE (d_date IN (CAST('2000-06-30' AS DATE)         , CAST('2000-09-27' AS DATE)         , CAST('2000-11-17' AS DATE)))\n"
                + "      ))\n"
                + "   ))\n"
                + "      AND (wr_returned_date_sk = d_date_sk)\n"
                + "   GROUP BY i_item_id\n"
                + ")\n"
                + "SELECT\n"
                + "  sr_items.item_id\n"
                + ", sr_item_qty\n"
                + ", CAST((((sr_item_qty / ((CAST(sr_item_qty AS DECIMAL(9,4)) + cr_item_qty) + wr_item_qty)) / CAST('3.0' AS DECIMAL(2,1))) * 100) AS DECIMAL(7,2)) sr_dev\n"
                + ", cr_item_qty\n"
                + ", CAST((((cr_item_qty / ((CAST(sr_item_qty AS DECIMAL(9,4)) + cr_item_qty) + wr_item_qty)) / CAST('3.0' AS DECIMAL(2,1))) * 100) AS DECIMAL(7,2)) cr_dev\n"
                + ", wr_item_qty\n"
                + ", CAST((((wr_item_qty / ((CAST(sr_item_qty AS DECIMAL(9,4)) + cr_item_qty) + wr_item_qty)) / CAST('3.0' AS DECIMAL(2,1))) * 100) AS DECIMAL(7,2)) wr_dev\n"
                + ", (((sr_item_qty + cr_item_qty) + wr_item_qty) / CAST('3.00' AS DECIMAL(5,2))) average\n"
                + "FROM\n"
                + "  sr_items\n"
                + ", cr_items\n"
                + ", wr_items\n"
                + "WHERE (sr_items.item_id = cr_items.item_id)\n"
                + "   AND (sr_items.item_id = wr_items.item_id)\n"
                + "ORDER BY sr_items.item_id ASC, sr_item_qty ASC\n"
                + "LIMIT 100\n");
        checkIfEquals(originalSql, equivalentSqlList);
    }

    /**
     * Test pre-materialized view rewrite need pre-rewrite when ELIMINATE_GROUP_BY_KEY_BY_UNIFORM applied
     * */
    @Test
    public void testNeedPreRewrite() {
        CascadesContext cascadesContext = MemoTestUtils.createCascadesContext("select T1.id from T1");
        StatementContext statementContext = cascadesContext.getConnectContext().getStatementContext();
        statementContext.setForceRecordTmpPlan(true);
        statementContext.ruleSetApplied(RuleType.ELIMINATE_GROUP_BY_KEY_BY_UNIFORM);
        statementContext.getPlannerHooks().add(InitMaterializationContextHook.INSTANCE);
        statementContext.getTmpPlanForMvRewrite().add(cascadesContext.getRewritePlan());
        Assertions.assertTrue(PreMaterializedViewRewriter.needPreRewrite(cascadesContext));
    }

    private void checkIfEquals(String originalSql, List<String> equivalentSqlList) {
        // init original cascades context
        CascadesContext originalCascadesContext = initOriginal(originalSql);
        // generate multi equivalent plan and correspond to relationId bit set
        Map<BitSet, LogicalPlan> bitSetLogicalPlanMap = generateEqualPlans(equivalentSqlList,
                originalCascadesContext.getRewritePlan());
        // copy multi plan to init memo and optimize
        CascadesContext cascadesContext = optimizeOriginal(originalCascadesContext, bitSetLogicalPlanMap);
        // extract plan from memo and check is equals or not
        Memo memo = cascadesContext.getMemo();
        for (Map.Entry<BitSet, LogicalPlan> planEntry : bitSetLogicalPlanMap.entrySet()) {
            memo.incrementAndGetRefreshVersion();
            StructInfo structInfo = memo.getRoot().getStructInfoMap().getStructInfo(cascadesContext,
                    planEntry.getKey(), memo.getRoot(), null, true);
            Assertions.assertNotNull(structInfo);
            Assertions.assertTrue(structInfo.getOriginalPlan().deepEquals(planEntry.getValue()));
        }
    }

    private CascadesContext initOriginal(String sql) {
        CascadesContext cascadesContext = createCascadesContext(sql, connectContext);
        PlanChecker.from(cascadesContext).analyze().rewrite();
        return cascadesContext;
    }

    private CascadesContext optimizeOriginal(CascadesContext cascadesContext, Map<BitSet, LogicalPlan> equivalentPlans) {
        // init memo
        cascadesContext.toMemo();
        Group root = cascadesContext.getMemo().getRoot();
        for (Plan plan : equivalentPlans.values()) {
            cascadesContext.getMemo().copyIn(plan, root, false);
        }
        // stats derive
        root.getLogicalExpressions().forEach(groupExpression ->
                cascadesContext.pushJob(new DeriveStatsJob(groupExpression, cascadesContext.getCurrentJobContext())));
        cascadesContext.getJobScheduler().executeJobPool(cascadesContext);
        // Cascades optimize
        cascadesContext.pushJob(
                new OptimizeGroupJob(root, cascadesContext.getCurrentJobContext()));
        cascadesContext.getJobScheduler().executeJobPool(cascadesContext);
        return cascadesContext;
    }

    private Map<BitSet, LogicalPlan> generateEqualPlans(List<String> sqlList, Plan originalPlan) {
        Map<BitSet, LogicalPlan> result = new HashMap<>();
        for (int i = 0; i < sqlList.size(); i++) {
            BitSet relationIdSet = new BitSet();
            String sql = sqlList.get(i);
            StatementContext statementCtx = createStatementCtx(sql, connectContext);
            for (int j = 0; j < (i + 1) * 1000; j++) {
                // make relation id is different between different plan
                statementCtx.getNextRelationId();
                statementCtx.getNextExprId();
                statementCtx.getNextCTEId();
            }
            CascadesContext context = MemoTestUtils.createCascadesContext(statementCtx, sql);
            PlanChecker.from(context).analyze().rewrite();
            Plan rewritePlan = context.getRewritePlan();
            rewritePlan.accept(
                    new DefaultPlanVisitor<Void, Void>() {
                        @Override
                        public Void visitLogicalRelation(LogicalRelation relation, Void context) {
                            relationIdSet.set(relation.getRelationId().asInt());
                            return null;
                        }
                    }, null
            );
            // normalize the same to originalPlan
            rewritePlan = rewritePlan.accept(new DefaultPlanRewriter<Plan>() {
                @Override
                public Plan visitLogicalResultSink(LogicalResultSink<? extends Plan> logicalResultSink,
                        Plan originalPlan) {
                    if (!originalPlan.getLogicalProperties().equals(logicalResultSink.getLogicalProperties())) {
                        Plan queryResultSinkChild = logicalResultSink.child();
                        Plan normalized =
                                MaterializedViewUtils.normalizeExpressions(queryResultSinkChild, originalPlan);
                        return new LogicalResultSink<>(normalized.getOutput().stream()
                                .map(NamedExpression.class::cast).collect(Collectors.toList()), normalized);
                    }
                    return logicalResultSink;
                }
            }, originalPlan);
            result.put(relationIdSet, (LogicalPlan) rewritePlan);
        }
        return result;
    }
}
